What it does
AVERAGEIFS averages values that meet multiple conditions.
Syntax or pattern
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)✅
5 practical examples
1
Average by region
Average sales for East region.
=AVERAGEIFS(Sales[Amount],Sales[Region],"East")Use one or more criteria pairs.
2
Average by date range
Average values in a selected period.
=AVERAGEIFS(Sales[Amount],Sales[Date],">="&F1,Sales[Date],"<="&G1)Good for report periods.
3
Average by product and status
Average completed order size for a product.
=AVERAGEIFS(Orders[Amount],Orders[Product],H2,Orders[Status],"Complete")Multiple criteria make the average more specific.
4
Average scores above zero
Ignore zero values.
=AVERAGEIFS(B2:B100,B2:B100,">0")Useful when zero means missing or not applicable.
5
Handle no matches
Avoid #DIV/0 when no rows match.
=IFERROR(AVERAGEIFS(Sales[Amount],Sales[Region],H2),"No data")A clear fallback helps report readers.
Common mistakes to avoid
- Selecting ranges with different sizes.
- Using text values where numbers are required.
- Forgetting how blanks and hidden rows are treated.
Related Excel examples
FAQ
Can this formula use Excel Tables?
Yes. Structured references often make summary formulas easier to read.