FormulaMath
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
=IFERROR(AVERAGEIFS(Sales!D2:D500, Sales!B2:B500, "West"), "")
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.