Syntax
=AVERAGEIF(criteria_range, criterion, [average_range])
criteria_range- The cells to test.
criterion- The condition to match.
average_range- Optional. The cells to average. If omitted, criteria_range is averaged.
AVERAGEIF function in Google Sheets
AVERAGEIF returns the average of the values that meet one condition, such as average revenue in the West or the average of all scores above 50.
Practice AVERAGEIF
Task 1 of 2
What is the average score for students in class B? Answer in E2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
AVERAGEIF examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Revenue |
| 2 | Ava | West | Monitor | 12 | $2,400.00 |
| 3 | Liam | East | Keyboard | 30 | $1,500.00 |
| 4 | Maya | West | Mouse | 45 | $900.00 |
| 5 | Noah | North | Monitor | 8 | $1,600.00 |
| 6 | Zara | East | Monitor | 15 | $3,000.00 |
| 7 | Omar | West | Keyboard | 22 | $1,100.00 |
| Formula | Result | What it shows |
|---|---|---|
=AVERAGEIF(B2:B7, "West", E2:E7) | $1,466.67 | Average West revenue. |
=AVERAGEIF(E2:E7, ">1500") | $2,333.33 | Average of the bigger deals only. |
=AVERAGEIF(C2:C7, "Monitor", D2:D7) | 11.6666666667 | Average units per Monitor sale. |
Tips and common mistakes
- If nothing matches, AVERAGEIF returns #DIV/0!. Wrap it in IFERROR if that can happen.
Related functions
Learning step by step? AVERAGEIF is covered in Lesson 15: AVERAGEIF Function of the free course.