The AVERAGEIF function in Excel
AVERAGEIF works like SUMIF, but it averages instead of adding. Average order in the North, average score of one class, average price of one category.
The condition comes before the range to average, the same order as SUMIF. For several conditions, use AVERAGEIFS, which puts the average range first.
Syntax
=AVERAGEIF(range, criteria, [average_range])
| Argument | What it means |
|---|---|
| range | The cells to check against the condition. |
| criteria | The condition: "North", ">100", a cell reference, or a wildcard pattern. |
| average_rangeoptional | The cells to average. If left out, the cells in range are averaged. |
Examples
1. Average for one region
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | East average | |
| 2 | 1001 | North | 250 | Paid | 130 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
East has one order of 130.
2. Average of large amounts
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Average over 200 | |
| 2 | 1001 | North | 250 | Paid | 335 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
When the condition is about the numbers themselves, leave out average_range. 250 and 420 are above 200; their mean is 335.
3. Condition from a cell
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Region | South | |
| 2 | 1001 | North | 250 | Paid | Average | 90 | |
| 3 | 1002 | South | 90 | Paid | |||
| 4 | 1003 | North | 420 | Open | |||
| 5 | 1004 | East | 130 | Paid | |||
| 6 | 1005 | North | 75 | Paid |
South has one order of 90. Change G1 and the average follows.
Common mistakes
Mixing up the order with AVERAGEIFS.
=AVERAGEIF(C2:C6,B2:B6,"North")doesn't work.Fix: AVERAGEIF: condition range, condition, average range. AVERAGEIFS: average range first, then the pairs.
#DIV/0! because nothing matched. Every row failed the condition, so there is nothing to divide by.
Fix: Check the spelling of the criterion, or wrap the formula in IFERROR.
Frequently asked questions
How do I average with two conditions?
Use AVERAGEIFS, which takes as many range/condition pairs as you need.
Does AVERAGEIF ignore text in the average range?
Yes. Only numbers are averaged, the same as AVERAGE.
Related functions
- AVERAGEIFSAVERAGEIFS returns the mean of values that meet several conditions at once, such as paid orders from one region.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
More on AVERAGEIF
AVERAGEIF calculates the mean of only those cells that pass one test. It is the quickest way to answer questions such as the typical order value in one region, or the mean mark of one class.
How the three arguments work together
The first argument is the range Excel checks, the second is the condition, and the optional third is the range that is actually averaged. When you leave out the third one, Excel averages the checked cells themselves, which is handy for conditions on the numbers: =AVERAGEIF(B2:B9,">50") averages only the values above 50.
When the two ranges are different columns, make them the same size and start on the same row. Excel pairs cells starting from the top-left cell of the average range, so a shifted range pairs the wrong rows and gives a plausible but wrong number.
Ways to write the condition
=AVERAGEIF(A2:A9,"North",C2:C9)=AVERAGEIF(A2:A9,"N*",C2:C9)=AVERAGEIF(C2:C9,">="&E1)Wildcards work for text only: * for any run of characters and ? for exactly one. The [wildcards guide](/guides/wildcards) explains how to search for a real asterisk.
What is skipped, and the #DIV/0! error
Matching cells in the average range that are empty or contain text are left out of the calculation. Zeros are included. If no cell passes the test, there is nothing to divide, and the result is #DIV/0!.
That error is information, not a fault. It says no rows matched, often because of a typo or a stray space in the data. You can show a friendlier message with IFERROR, but check the criteria first so you do not hide a real mistake.
=IFERROR(AVERAGEIF(A2:A9,E1,C2:C9),"No data")When AVERAGEIF is not enough
It handles a single condition. For two or more, such as paid orders in one region, move to AVERAGEIFS, where the range to average comes first. Carrying over the argument order from AVERAGEIF is a common cause of wrong or empty results.
To compare groups, put each region in its own row and fill the same formula down with a locked range, as in =AVERAGEIF($A$2:$A$9,E2,$C$2:$C$9). The lesson on [conditional sums](/learn/conditional-sums) explains the same pattern.