Excel function · Statistics
AVERAGEIF
Averages the numbers whose row meets one condition.
What it does
AVERAGEIF works like SUMIF, but divides the total by the number of matching rows. You give it the range to test, the condition, and the numbers to average.
Syntax
=AVERAGEIF(range, criteria, [average_range])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- range
- The cells to test against the condition.
- criteria
- The condition, such as "Food", ">100" or a cell.
- average_range
- The numbers to average. Leave it out to average the tested cells themselves.
Example
What is the average food bill?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Average food | |
| 2 | Food | 18 | 17 | |
| 3 | Hotel | 90 | ||
| 4 | Food | 12 | ||
| 5 | Food | 21 |
The formula in D2:
=AVERAGEIF(A2:A5,"Food",B2:B5)Result: 17
Three rows are Food: 18, 12 and 21. Their total is 51, and 51 divided by 3 is 17. The hotel row is ignored.
Common mistakes
- Making the two ranges different sizes, so values are matched to the wrong rows.
- Not expecting
#DIV/0!when nothing matches. Wrap it inIFERRORif that can happen.
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 | AVERAGEIF | , |
| Deutsch | MITTELWERTWENN | ; |
| Français | MOYENNE.SI | ; |
| Español (España) | PROMEDIO.SI | ; |
| Español (México) | PROMEDIO.SI | , |
| 日本語 | AVERAGEIF | , |
| 한국어 | AVERAGEIF | , |
| 繁體中文 | AVERAGEIF | , |
| ไทย | AVERAGEIF | , |
| ខ្មែរ | AVERAGEIF | , |
| Tiếng Việt | AVERAGEIF | ; |