What it does
AVERAGEIF takes its arguments in the same order as SUMIF: the range to test, the condition and the range to average. =AVERAGEIF(B2:B20,"West",C2:C20) averages column C for the rows whose region is West. If the last range is left out, the tested range itself is averaged.
One of its handiest uses is leaving zeros out: =AVERAGEIF(B2:B13,"<>0") ignores months without sales. Anything that works in a COUNTIF condition works here too: comparison operators, the * and ? wildcards and cell references.
If no row meets the condition, the result is #DIV/0!. For several conditions use AVERAGEIFS; its argument order is different, with the range to average first.
Syntax
=AVERAGEIF(range, criteria, [average_range])=EĞERORTALAMA(aralık; ölçüt; [ortalama_aralığı])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
range | Required | The cells to test, e.g. the region column B2:B20. |
criteria | Required | The condition: "West", ">=1000", "<>0", "KBL*" or a cell (E2). Case is ignored. |
average_range | Optional | The cells to average; the same size as range. If omitted, range itself is used. |
Examples
1Average order value per region
The regions are in column E. The formula in F2 is filled down; the test range and the average range are locked with $, so only the condition cell moves.
=AVERAGEIF($B$2:$B$8,E2,$C$2:$C$8)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Region | Average | |
| 2 | S-1001 | West | 12,500.00 | West | 6,783.50 | |
| 3 | S-1002 | North | 8,300.00 | North | 11,750.00 | |
| 4 | S-1003 | West | 4,750.50 | South | 8,150.00 | |
| 5 | S-1004 | South | 9,900.00 | |||
| 6 | S-1005 | West | 3,100.00 | |||
| 7 | S-1006 | North | 15,200.00 | |||
| 8 | S-1007 | South | 6,400.00 |
- F2 (filled down to F4)
=AVERAGEIF($B$2:$B$8,E2,$C$2:$C$8)
The West's three orders average 6,783.50, the North's 11,750.00 and the South's 8,150.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Leave months without sales out of the average
The store was closed for refurbishment in March and April, and sales were entered as 0. D2 averages all months; E2 averages only the months that are not zero.
=AVERAGE(B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Average | Excluding Zeros | |
| 2 | January | 84,000 | 59,833 | 89,750 | |
| 3 | February | 91,500 | |||
| 4 | March | 0 | |||
| 5 | April | 0 | |||
| 6 | May | 88,200 | |||
| 7 | June | 95,300 |
- D2
=AVERAGE(B2:B7) - E2
=AVERAGEIF(B2:B7,"<>0")
The average over all months is 59,833; the average for the months the store was open is 89,750.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Average of orders above a set amount
Without an average range, the tested range itself is averaged. E2 returns the average of orders above 5,000.
=AVERAGE(B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount | All Orders | Over 5,000 | |
| 2 | S-1001 | 12,500.00 | 7,248.42 | 12,533.33 | |
| 3 | S-1002 | 830.00 | |||
| 4 | S-1003 | 4,750.50 | |||
| 5 | S-1004 | 9,900.00 | |||
| 6 | S-1005 | 310.00 | |||
| 7 | S-1006 | 15,200.00 |
- D2
=AVERAGE(B2:B7) - E2
=AVERAGEIF(B2:B7,">5000")
All orders average 7,248.42; the large orders average 12,533.33. The small orders pull the overall average down.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #DIV/0!
Cause: No row meets the condition, or the matching rows are empty in the average range.
Fix: Check how the condition is spelled; to show text instead of the error, use
=IFERROR(AVERAGEIF(B2:B20,"West",C2:C20),"No sales").- Shifted average
Cause: average_range starts on a different row from range (
B2:B20againstC3:C21), so rows are paired up wrongly.Fix: Start and end both ranges on the same rows.
Good to know
- Put the condition in a cell and fill the formula down to get every region's average in one go; lock the test range and the average range with
$. - To see how many rows an average is based on, add a COUNTIF column with the same condition.
In Nexell
Get Nexell to do it from a sentence
Make a small summary table with the average order value of each region
Convert to tableBring your order list into the tool and type this. Nexell builds the summary with conditional average formulas and shows it in the preview first; in the downloaded file, the averages change when an order changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I average excluding zeros in Excel?
Use =AVERAGEIF(B2:B13,"<>0"). Zeros fail the condition and are skipped; empty cells are left out anyway.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and the average range comes last. AVERAGEIFS takes several conditions and the average range comes first.