AVERAGEIF and AVERAGEIFS in Excel: Average by Condition
Use AVERAGEIF and AVERAGEIFS in Excel to average only matching rows: by category, excluding zeros, between dates, with the #DIV/0! case handled cleanly.
By Ayoub Farid · · 1 min read
AVERAGEIF works like AVERAGE, but only for rows that meet a condition. Average order value for one region. Average score for one class. Average spend excluding zero months.
Syntax
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)Same pattern as SUMIF and SUMIFS: the multi-condition version puts the values first.
Average by category
| A | B | |
|---|---|---|
| 1 | Category | Price |
| 2 | Coffee | 2.50 |
| 3 | Coffee | 3.50 |
| 4 | Food | 2.80 |
| 5 | Food | 3.00 |
=AVERAGEIF(A2:A5,"Food",B2:B5) → 2.90Exclude zeros
=AVERAGEIF(B2:B13,"<>0")Useful when months without data are typed as 0.
Several conditions
North region, Coffee only:
=AVERAGEIFS(D2:D500,B2:B500,"North",C2:C500,"Coffee")Between two dates (start in G1, end in G2):
=AVERAGEIFS(D2:D500,A2:A500,">="&G1,A2:A500,"<="&G2)Handle "no matches"
If nothing matches, AVERAGEIF returns #DIV/0! (it can't divide by zero rows). Wrap it:
=IFERROR(AVERAGEIF(A2:A100,F2,B2:B100),"No data")Common mistakes
- Expecting AVERAGEIF to ignore blanks in the condition column: blanks only match
""criteria. - Mixing up argument order between AVERAGEIF and AVERAGEIFS.
- Averaging percentages from groups of different sizes. That needs a weighted average.
Frequently asked questions
How do I average only certain cells in Excel?
Use AVERAGEIF with a condition, e.g. =AVERAGEIF(A:A,"Food",B:B).
How do I average excluding zeros?
=AVERAGEIF(range,"<>0").
Why does AVERAGEIF return #DIV/0!?
No rows matched the condition. Wrap it in IFERROR.
Does Google Sheets have AVERAGEIFS?
Yes, with the same syntax.