How to Calculate the Average in Excel: AVERAGE, AVERAGEIF, and More

Excel makes it easy to calculate averages — but "average" means different things depending on what you're working with. Whether you're averaging a simple list of numbers or calculating a conditional average across thousands of rows, Excel has a function for it. Here's how each one works, and what factors shape which approach fits your data.

What "Average" Actually Means in Excel

In everyday use, "average" refers to the arithmetic mean — add up all the values, then divide by how many there are. Excel's built-in functions automate that calculation, but they also give you control over which values get included, which get excluded, and whether any conditions apply.

Understanding which function to use starts with understanding your data.

The Core Function: AVERAGE

The simplest way to calculate an average in Excel is with the AVERAGE function.

Syntax:

=AVERAGE(number1, [number2], ...) 

Example:

=AVERAGE(B2:B20) 

This adds all numeric values in cells B2 through B20 and divides by the count of those values. It's clean, fast, and works for most basic use cases.

Important behavior to know:

• AVERAGE ignores blank cells — they don't count toward the total or the divisor
• AVERAGE ignores text values in the range
• AVERAGE does include cells containing zero — a zero is a value, not a blank

This last point catches a lot of users off guard. If you have a zero where data is missing, your average will be pulled down in a way a blank cell wouldn't cause.

When You Need Conditions: AVERAGEIF

If you want an average that only includes values meeting a specific criterion, use AVERAGEIF.

Syntax:

=AVERAGEIF(range, criteria, [average_range]) 

Example — average sales figures only for the "East" region:

=AVERAGEIF(A2:A100, "East", C2:C100) 

Here:

• A2:A100 is the criteria range (where Excel checks the condition)
• "East" is the criteria (what it's looking for)
• C2:C100 is the average range (what gets averaged when the condition is met)

You can use wildcards (*, ?) and comparison operators (>, <, >=) as criteria too:

=AVERAGEIF(C2:C100, ">500") 

This averages only values in C2:C100 that are greater than 500.

Multiple Conditions: AVERAGEIFS

When your average depends on more than one condition, step up to AVERAGEIFS.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 

Example — average sales for the East region in Q1:

=AVERAGEIFS(C2:C100, A2:A100, "East", B2:B100, "Q1") 

Note that unlike AVERAGEIF, the average range comes first in AVERAGEIFS — a common source of formula errors when switching between the two.

Averaging Visible Cells Only: SUBTOTAL and AGGREGATE 📊

If your spreadsheet uses filters, AVERAGE will still calculate across hidden rows. To average only the visible (filtered) cells, use SUBTOTAL:

=SUBTOTAL(101, C2:C100) 

Function number 101 tells SUBTOTAL to average while ignoring hidden rows. (Function number 1 would average but include hidden rows.)

For more complex scenarios — like ignoring error values or nested subtotals — AGGREGATE gives you even more control, though it has a steeper learning curve.

Weighted Averages: When Not All Values Are Equal

The AVERAGE function treats every value equally. If some values carry more weight than others — like grades worth different percentages, or prices with different quantities — you need a weighted average.

Excel doesn't have a dedicated WEIGHTED AVERAGE function, but you can build one using SUMPRODUCT:

=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10) 

Where B2:B10 holds the values and C2:C10 holds the weights. SUMPRODUCT multiplies each value by its weight, sums the results, and divides by the total weight.

Comparing the Main Approaches

Factors That Affect Which Approach Works for You

Several variables determine which function actually fits your situation:

Data structure — A flat list of numbers calls for AVERAGE. A table with category columns usually needs AVERAGEIF or AVERAGEIFS. A pivot table or filtered report may require SUBTOTAL.

Presence of zeros vs. blanks — If your dataset uses zeros as placeholders for missing data, your AVERAGE results will differ significantly from datasets that use blank cells. How your data was entered shapes what you get back.

Error values in the range ⚠️ — If any cell in your range contains an error like #DIV/0! or #N/A, AVERAGE will return an error too. Use AVERAGEIF with a numeric criterion, or wrap your formula in IFERROR, to handle this gracefully.

Excel version — AVERAGEIFS was introduced in Excel 2007. AGGREGATE arrived in Excel 2010. If you're working with older .xls files or sharing workbooks with users on older versions, function availability can matter.

Dataset size — For very large datasets, complex AVERAGEIFS formulas or volatile functions can slow recalculation. In those cases, helper columns or Power Query transformations may be more efficient than stacking conditions inside a single formula.

The right approach depends on whether your data is clean, how it's structured, what version of Excel you're running, and what question you're actually trying to answer — because an average is only as meaningful as the data it's built from.