AVERAGEIFS
Calculates the average over the rows that match all conditions at once: the average salary of one department in one city.
AVERAGEIFS(average_range, criteria_range1, criteria1, ...)- average_range
- Which numbers to average, for example salaries in C2:C8.
- criteria_range1
- Where to check the first condition, for example departments in A2:A8.
- criteria1
- The first condition. Then you add a second range and a second condition the same way.
Return value
A number: the average over the rows that matched all conditions. If there are no such rows, you get a #DIV/0! error.
Examples
Average salary of a department in a city
fx
A
B
C
D
E
F
G
H
1
2
3
4
5
6
7
8
9
10
Department
City
Salary, $
Department
City
Average, $
Sales
Boston
95000
Sales
Boston
Sales
Portland
62000
Sales
Portland
Warehouse
Boston
70000
Warehouse
Boston
Sales
Boston
105000
Warehouse
Boston
64000
Sales
Portland
58000
Sales
Boston
88000
Sheet1
Details
AVERAGEIFS is built like SUMIFS: the range of numbers to average comes first, followed by pairs of “where to check and what condition”.
A row counts only if all conditions are met. If no row matches, you get a #DIV/0! error.
Common mistakes
- The order of arguments is mixed up with AVERAGEIF: here the range of numbers comes first.
- No row matched all conditions: instead of an average you get a #DIV/0! error.
- Ranges of different lengths: the formula returns a #VALUE! error.