Skip to content

Excel function · Statistics

Excel AVERAGEIF function

AVERAGEIF averages the rows that meet a condition: the average order value of one region, the average of the months with sales, the average of orders above a set amount.

In Turkish Excel: EĞERORTALAMA

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

English Excel=AVERAGEIF(range, criteria, [average_range])
Turkish Excel=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

ArgumentRequiredDescription
rangeRequiredThe cells to test, e.g. the region column B2:B20.
criteriaRequiredThe condition: "West", ">=1000", "<>0", "KBL*" or a cell (E2). Case is ignored.
average_rangeOptionalThe 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.

F2fx=AVERAGEIF($B$2:$B$8,E2,$C$2:$C$8)
Average order value per region: example table
ABCDEF
1OrderRegionAmountRegionAverage
2S-1001West12,500.00West6,783.50
3S-1002North8,300.00North11,750.00
4S-1003West4,750.50South8,150.00
5S-1004South9,900.00
6S-1005West3,100.00
7S-1006North15,200.00
8S-1007South6,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.

D2fx=AVERAGE(B2:B7)
Leave months without sales out of the average: example table
ABCDE
1MonthSalesAverageExcluding Zeros
2January84,00059,83389,750
3February91,500
4March0
5April0
6May88,200
7June95,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.

D2fx=AVERAGE(B2:B7)
Average of orders above a set amount: example table
ABCDE
1OrderAmountAll OrdersOver 5,000
2S-100112,500.007,248.4212,533.33
3S-1002830.00
4S-10034,750.50
5S-10049,900.00
6S-1005310.00
7S-100615,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:B20 against C3: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

Type this in Nexell

Make a small summary table with the average order value of each region

Convert to table

Bring 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.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free