Excel functions / Maths & statistics
AVERAGEIF & AVERAGEIFS
Averages only the values that meet one or more conditions.
Syntax
=AVERAGEIF(range, criteria, [average_range])Arguments
range | The cells to test. |
|---|---|
criteria | The condition to meet. |
average_range | Optional. The cells to average. |
How it works
AVERAGEIF works just like SUMIF, but returns the mean of the matching values. AVERAGEIFS takes several conditions, with the average range first: =AVERAGEIFS(C2:C5,A2:A5,"East",C2:C5,">200").
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | 375 | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: 375. The average of the two East sales: (500 + 250) / 2.
Tips
- If no rows match, you get #DIV/0!. IFERROR can replace it with a message.
Practise AVERAGEIF & AVERAGEIFS
Try it yourself in a real spreadsheet, with instant feedback on every answer.