Excel function · Statistics
AVERAGEIFS
Averages the numbers whose row meets several conditions.
What it does
AVERAGEIFS averages only the rows that meet every condition you give. Like SUMIFS, the range to average comes first, then pairs of range and condition.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- average_range
- The numbers to average.
- criteria_range
- A range to test, the same size as the average range.
- criteria
- The condition for that range.
Example
What do red items in size Large cost on average?
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Colour | Size | Price | Red and large | |
| 2 | Red | Large | 24 | 26 | |
| 3 | Blue | Large | 20 | ||
| 4 | Red | Small | 14 | ||
| 5 | Red | Large | 28 |
The formula in E2:
=AVERAGEIFS(C2:C5,A2:A5,"Red",B2:B5,"Large")Result: 26
Two rows are both Red and Large: 24 and 28. Their average is 26. The blue large one and the red small one don't meet both conditions.
Common mistakes
- Putting the average range last, as in
AVERAGEIF. In the IFS version it comes first. - Expecting OR logic. Every condition must be true for a row to count.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | AVERAGEIFS | , |
| Deutsch | MITTELWERTWENNS | ; |
| Français | MOYENNE.SI.ENS | ; |
| Español (España) | PROMEDIO.SI.CONJUNTO | ; |
| Español (México) | PROMEDIO.SI.CONJUNTO | , |
| 日本語 | AVERAGEIFS | , |
| 한국어 | AVERAGEIFS | , |
| 繁體中文 | AVERAGEIFS | , |
| ไทย | AVERAGEIFS | , |
| ខ្មែរ | AVERAGEIFS | , |
| Tiếng Việt | AVERAGEIFS | ; |