WHAT DOES AVERAGEIF DO
The AVERAGEIF function calculates the average of cells that meet a single condition. It’s useful when you want to average a subset of your data based on a filter.
For example, you can average sales amounts where the region is East.
WHEN TO USE IT
- You want to calculate an average based on a single condition
- You need a simpler alternative to
AVERAGEIFS - Your data is organized in a consistent row/column format
How is this different from AVERAGEIFS?AVERAGEIF only supports one condition. If you need to filter by multiple criteria, use AVERAGEIFS instead.
FORMULA SYNTAX
AVERAGEIF(range, criteria, [average_range])| Argument | Description |
|---|---|
range | The range to evaluate with the condition |
criteria | The condition to apply (e.g. ">=80", "East") |
average_range | (optional) Range to average — defaults to range if omitted |
EXAMPLE 1: AVERAGE WHERE REGION IS EAST
Average all sales where Region = East.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | East | A | 100 |
| 3 | West | A | 200 |
| 4 | East | B | 150 |
| 5 | North | A | 250 |
=AVERAGEIF(A2:A5, "East", C2:C5)Result: (100 + 150) / 2 = 125
EXAMPLE 2: AVERAGE SALES OVER $200
Average all sales greater than $200.
| A | B | |
|---|---|---|
| 1 | Sales | Region |
| 2 | 100 | East |
| 3 | 250 | West |
| 4 | 300 | East |
| 5 | 200 | North |
=AVERAGEIF(A2:A5, ">200")Result: (250 + 300) / 2 = 275
COMMON PITFALLS AND EDGE CASES
- If
average_rangeis omitted, the function averages values inrange - Quotes are required for logical operators (e.g.
">100","North") - Blank cells in the
average_rangeare ignored - If no values match the condition, the result is
#DIV/0! - AVERAGEIF does not support multiple conditions — use AVERAGEIFS if needed
RELATED FUNCTIONS
FURTHER READING
Official Microsoft documentation:
AVERAGEIF – Microsoft Support