Function guides · Statistical
AVERAGEIF function
Averages the values in a range that meet one condition.
Syntax
=AVERAGEIF(range, criteria, [average_range])
| Argument | What it is |
|---|---|
range | The cells to test against the condition. |
criteria | The condition, such as "West" or ">100". |
average_range | Optional. The cells to average. If omitted, range itself is averaged. |
What AVERAGEIF does
AVERAGEIF averages the values that sit beside cells meeting a condition. It answers questions of the form “what is the typical X for Y”.
You give it the range to test, the condition and, if different, the range to average. When the cells you test are the cells you average, the last argument can be left out, as in the zero-exclusion example below.
Criteria follow the same rules as COUNTIF and SUMIF: text matches ignore case, comparisons go inside quotes, and wildcards are allowed.
It returns #DIV/0! when nothing matches, which is a useful signal and not always an error to hide.
AVERAGEIF examples
1. Average sale for one region
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 450 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 280 |
Excel tests column A for "West" and averages the matching amounts from column B: 450, 620 and 280. That is 1,350 divided by 3.
2. Leave zeros out of the average
| A | B | C | |
|---|---|---|---|
| 1 | Store | Sales | |
| 2 | North | 300 | 400 |
| 3 | South | 0 | |
| 4 | East | 500 | |
| 5 | West | 0 | |
| 6 | Central | 400 |
Two stores reported no sales and typed 0. A plain AVERAGE would count them. The criterion "<>0" averages only the stores that actually traded: 300, 500 and 400.
3. Average by a partial name
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | |
| 2 | ProDesk | 240 | 150 |
| 3 | Mat | 20 | |
| 4 | ProLamp | 60 |
Wildcards work as they do in COUNTIF. The pattern "Pro*" matches any product that starts with Pro, so the average covers ProDesk and ProLamp but not the Mat.
Common AVERAGEIF mistakes
- #DIV/0! when nothing matches. If no cell meets the criteria there is nothing to average. Wrap in IFERROR if that is a normal outcome, or check the spelling of the criterion.
- Swapping argument roles. AVERAGEIF is range, criteria, average_range. AVERAGEIFS puts the average range first. Moving a formula from one to the other without reordering gives wrong results.
- Quoting problems. A comparison such as >300 must be inside quotes: ">300". Joining a cell with the operator needs an ampersand: ">"&E1.
- Using it for two conditions. AVERAGEIF takes only one. Use AVERAGEIFS for more.
AVERAGEIF on Excel job tests
"What is the average X for Y": the phrase after "for" or "in" is the condition, and the phrase after "average" is the range to average. Average order value for the West, average score in section B, average salary above a threshold.
If the task says to ignore zeros or missing values, use "<>0" or ">0" as the criterion, depending on whether negatives are possible. A region spelled with a trailing space in the data will not match, so look at the data once before blaming the formula.
Do not round unless the task asks. If it does, wrap the whole function in ROUND.
Related functions
AVERAGEIF questions
- What is the difference between AVERAGEIF and AVERAGEIFS?
- AVERAGEIF takes one condition with the average range last. AVERAGEIFS takes many conditions and puts the average range first.
- How do I average only the values above a number?
- Use a comparison criterion:
=AVERAGEIF(B2:B20,">300"). - Why does AVERAGEIF return
- No cell matched the criteria. Check the spelling, and trailing spaces in the data.
- Does AVERAGEIF ignore blank cells?
- Blank cells in the average range are ignored. A blank in the criteria range does not match a text criterion.
Practice AVERAGEIF
The lesson AVERAGEIF and AVERAGEIFS (module 8 of Everyday Excel) has checked AVERAGEIF exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample