AVERAGEIF
Calculates the average only over the rows that match a condition: the average order in one city, the average score in one course.
AVERAGEIF(range, criteria, [average_range])- [ ]optional part
- range
- Where to check the condition, for example cities in A2:A7.
- criteria
- The condition itself: text, a number or a comparison in quotes.
- average_range
- Which numbers to average. If omitted, the condition range itself is averaged.Optional
Return value
A number: the average of the cells that matched the condition. If none matched, you get a #DIV/0! error.
Examples
Average order by city
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
City
Amount, $
City
Avg order, $
Boston
7200
Boston
Denver
3400
Denver
Boston
2600
Austin
8100
Denver
4600
Boston
5400
Sheet1
Details
AVERAGEIF is built like SUMIF: first the range where the condition is checked, then the condition itself, then the numbers to average.
The function skips empty cells and text in the number range. If no row matches the condition, you get a #DIV/0! error.
Common mistakes
- No row matched (for example, a typo in the city name): instead of an average you get a #DIV/0! error.
- Zeros in the data count and pull the average down, while empty cells are skipped.
- The order of arguments: the numbers to average come last, as in SUMIF.