AVERAGEIF Generator
Average only the cells whose matching row meets a condition.
AVERAGEIF
Average only the cells whose matching row meets a condition.
Range tested against the criteria.
Condition to match, for example "West", ">100" or "<>0". Text and comparisons need quotes.
Numbers to average on matching rows. Excel treats this argument as optional, but do not leave the box empty - repeat the criteria range here when you test and average the same column. Give it the same size as the criteria range.
=AVERAGEIF(A2:A100, "West", C2:C100)Worked Example
Region names in column A and deal values in column C; you want the average deal size in the West region.
=AVERAGEIF(A2:A100, "West", C2:C100)Returns: Returns the mean of the column C values on rows where column A is West, ignoring blanks in C. If no row says West, it returns #DIV/0!.
Checks Before You Paste
- •AVERAGEIF returns #DIV/0! when no row matches, and also when the matching rows contain no numbers at all, because there is nothing to divide by. That is expected behaviour, not a broken formula, so wrap it in IFERROR if a dashboard needs a blank or a dash instead.
- •Blank cells in the average range are skipped rather than treated as zero, and so are cells holding TRUE or FALSE. The denominator is the count of matching rows that actually contain a number, which is why the result can differ from a hand-built SUM/COUNT.
- •If the average range is smaller than the criteria range, Excel silently stretches it from its top-left cell to match the criteria range's size and shape, quietly averaging cells you never named. Always enter both ranges at the same size; AVERAGEIFS rejects the mismatch outright instead of guessing.