The AVERAGEIFS function
The average North basket in March: an average over only the rows that meet every criterion.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, …)
In a French Excel: =MOYENNE.SI.ENS(plage_moyenne; plage_critères1; critère1; …).
Arguments
- average_range: the column to average, first.
- criteria_range1, criteria1…: pairs of tested column and criterion.
An example
=AVERAGEIFS(D:D,A:A,"North",B:B,"Delivered")
The average amount of delivered North orders.
Pitfalls
- No row matches: #DIV/0!.
- Order: the average range comes first, the reverse of AVERAGEIF.
Going further
Frequently asked questions
- How do I avoid #DIV/0!?
- Wrap it in IFERROR:
=IFERROR(AVERAGEIFS(…),""), or first test the number of rows with COUNTIFS.