Does AVERAGEIF include blank cells?

No. Empty cells in the average range are ignored.

What if no cells match?

It returns #DIV/0!. Use IFERROR to show a message.

AVERAGEIF function in Excel — syntax and example

Syntax

AVERAGEIF(range, criteria, [average_range])

Example: =AVERAGEIF(A:A,"Denver",C:C)

Available in: All versions

AVERAGEIF returns the average of the cells that meet one condition: =AVERAGEIF(range, criteria, [average_range]). Use it to find the average sale for one rep, the average score above a threshold, or the average price for one category.

When to use it

You need an average for a single category without filtering.
You want to average only values above or below a limit.
You exclude zeros or blanks from an average with "<>0".
For more than one condition use AVERAGEIFS.

Arguments explained

range. The cells that are tested against the criteria.
criteria. The condition, such as "East", ">100" or a cell reference. Wildcards work for text.
average_range. Optional. The cells that are averaged. If you omit it, the values in range are averaged. Blank and text cells in it are ignored.
Errors. If nothing matches the criteria, the result is #DIV/0!. Wrap the formula in IFERROR when that can happen.

Worked examples

Average for one region

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =AVERAGEIF(A2:A7,"West",C2:C7)

Result: 1175

West rows are 850 and 1500, giving 1175.

Average of larger amounts only

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =AVERAGEIF(C2:C7,">500")

Result: 1026

Five of the six amounts exceed 500; their sum 5130 divided by 5 is 1026.

Average for one rep

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =AVERAGEIF(B2:B7,"Ana",C2:C7)

Result: 905

Ana's two amounts are 1200 and 610.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =AVERAGEIF(A2:A7,"East",C2:C7)

Example spreadsheet for AVERAGEIF
	A	B	C	D	E	F
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		60
3	West	Pen	80	2.5
4	East	Book	45	12
5	North	Book	60	12
6	West	Lamp	30	25
7	East	Lamp	15	25

Result in F2: 60

Practice this in a lesson: Multiple Conditions

Common errors and how to fix them

#DIV/0!. No cell met the condition, so there is nothing to average. Wrap in IFERROR if that is possible.
Wrong average. The average_range must align with the range. Misaligned sizes shift which cells are used.
Text numbers ignored. Cells containing text numbers are skipped. Convert them to real numbers.
Zeros pull the average down. Add "<>0" as an extra criteria with AVERAGEIFS if zeros should be excluded.

Related functions

Frequently asked questions

How do I average ignoring zeros?: Use =AVERAGEIF(range,"<>0").
Does AVERAGEIF include blank cells?: No. Empty cells in the average range are ignored.
What if no cells match?: It returns #DIV/0!. Use IFERROR to show a message.
Can AVERAGEIF take two conditions?: No, use AVERAGEIFS.