Average values that meet a condition
Average a column filtered by a condition, guarded so a result with zero matching rows shows blank instead of a division error.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →AVERAGEIFS puts the range being averaged FIRST, then the criteria pairs — the opposite order from AVERAGEIF, which asks for the criteria range first. Mixing the two up quietly averages the wrong column.
- →The IFERROR wrapper isn't decoration: AVERAGEIFS divides by the count of matching rows, so zero matches means a division by zero, and this catches it before it reaches a dashboard.
- →Blank cells inside avg_range are excluded automatically, so a partially filled column doesn't drag the average toward zero.
The fine print
- →With no matching rows, AVERAGEIFS on its own returns #DIV/0! — that's the whole reason for the IFERROR wrapper here.
- →criteria_range and avg_range must be the same size, exactly like COUNTIFS and SUMIFS.
- →Works the same way in Excel and Google Sheets, IFERROR included.
Part by part
- =IFERROR(
- The wrapper. It exists because AVERAGEIFS returns #DIV/0! when nothing matches, which is mathematically correct and useless in a report.
- AVERAGEIFS(
- Averages the values in one range for rows meeting conditions in others. The averaged range comes first, unlike in SUMIF where it comes last.
- {{avg_range}}
- The numbers to average. Blank cells here are ignored rather than treated as zero, which changes the result and is rarely mentioned.
- {{criteria_range}}, {{criteria}}
- A test pair, applied row by row. Further pairs can follow and are combined with AND.
- ), "")
- The fallback: an empty string rather than a zero, so a category with no data reads as blank instead of implying an average of nothing.
Common mistakes
=AVERAGEIFS(B:B, A:A, "East")Without the wrapper, a category with no matching rows shows #DIV/0! and any chart or total built on it breaks too. The error is correct and it should not reach the reader.
=IFERROR(AVERAGEIFS(B:B, A:A, "East"), "")=IFERROR(AVERAGEIFS(B:B, A:A, "East"), 0)Falling back to zero states that the average is zero, which is a different claim from having no data. It also drags down any average computed over these results.
=AVERAGEIF(A:A, "East", B:B)The singular form takes its arguments in the opposite order — range, criterion, then the values to average. Mixing up the two signatures averages the wrong column, and often produces a plausible number.
Questions
Why does it return #DIV/0! rather than zero?
Because an average of nothing is undefined, not zero. That distinction is right, and it is why the IFERROR wrapper is part of the recipe rather than an afterthought.
Are blank cells treated as zero?
No, they are excluded from the calculation entirely. A cell containing a zero is included, so blank and zero produce different averages — worth checking when a figure looks higher than expected.
How do I average between two dates?
Use two pairs on the same range: one criterion of ">=" & start and another of "<=" & end. Build both by concatenating cell references so the report can be re-dated without editing formulas.
Should I use AVERAGEIFS or a pivot table?
For a handful of fixed categories, formulas are clearer and update live. Once you are writing the same formula for a dozen categories, a pivot table is less work and far less error-prone.