Functions
AVERAGEIFS
Conditional average with multiple criteria
When to use it
When should I use AVERAGEIFS instead of AVERAGEIF?
Use AVERAGEIFS when the average must satisfy more than one condition at once, such as average salary where department is Sales AND tenure is over two years. AVERAGEIF handles only a single criterion.
How does AVERAGEIFS treat rows that match only some of the conditions?
AVERAGEIFS averages a cell only when every criteria range matches its criterion for that row. A row that satisfies some but not all conditions is excluded, and if no rows match it returns the #DIV/0! error.
What is the argument order for AVERAGEIFS?
It starts with the average range, then pairs of criteria range and criterion: AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2, ...). This differs from AVERAGEIF, where the range to average comes last.
Used in
Syntax
AVERAGEIFS(avg_range,criteria_range1,criteria1,...)