The AVERAGEIF function calculates the average of cells that meet a single condition. This guide explains how AVERAGEIF works and includes practical examples you can use immediately.
WHAT THE AVERAGEIF FUNCTION DOES
AVERAGEIF computes the mean of values that match one specific criterion, such as averaging sales amounts where the region is East, or calculating the average score for students who passed. It's useful when you want to analyze a filtered subset of your data.
For example, you can average sales amounts where the region is East.
WHEN TO USE AVERAGEIF
Use AVERAGEIF when you want to:
- Calculate an average based on a single condition
- Average a subset of your data based on a filter
- Use a simpler alternative to AVERAGEIFS
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
Need help applying this? Try this in Numstro Builder
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
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & 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
- AVERAGEIFS — average with multiple conditions
- SUMIF — sum with one condition
- COUNTIF — count with one condition
FURTHER READING
Official Microsoft documentation:
AVERAGEIF — Microsoft Support