Excel guide · 4 min read

Average excluding zeros (and blanks) in Excel

Q: How do I average a range and ignore zeros in Excel?

Use =AVERAGEIF(range,">0") for values that are never negative, or =AVERAGEIF(range,"<>0") when negatives are possible. Both skip empty cells too.

Q: How do I average without zeros and with another condition?

Use AVERAGEIFS: =AVERAGEIFS(C2:C11,B2:B11,"York",C2:C11,"<>0"). The first range is what you average, and the value column appears again with "<>0".

Quick answerUse =AVERAGEIF(C2:C11,">0") to average only the values above zero. If your numbers can be negative, use =AVERAGEIF(C2:C11,"<>0") instead, which drops only the zeros. Both ignore empty cells automatically.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D
1	Month	Shop	Units sold	Profit
2	Jan	Leeds	420	3150
3	Jan	York	0	-800
4	Feb	Leeds	385	2480
5	Feb	York	0	0
6	Mar	Leeds	410	2900
7	Mar	York	310	1720
8	Apr	Leeds	455	-300
9	Apr	York	362	2100
10	May	Leeds
11	May	York	298	1540

Download this sheet as an Excel practice file (.xlsx)

AVERAGEIF ">0" for counts and sales

The York shop was closed for a refit in January and February, so its units sold are 0. Those zeros are not real sales results, and a plain AVERAGE counts them. Leeds has not reported May yet, so C10 is empty.

AVERAGE(C2:C11) gives 293.33: it adds the nine numbers and divides by 9, skipping the empty cell but counting both zeros. AVERAGEIF with ">0" divides by 7, the months that actually traded.

=AVERAGEIF(C2:C11,">0")

Result: 377.14285714

377.14 units per shop-month, compared with 293.33 from AVERAGE.

Negative numbers: use "<>0", not ">0"

">0" means "greater than zero", so it throws away negative values too. For profit, temperature, stock changes or anything else that can go below zero, that silently turns your average into "the average of the good months".

Here AVERAGE gives 1,421.11 (nine values, including the 0 for February in York). ">0" gives 2,315 because it drops the two loss months. "<>0" keeps the losses and drops only the zero, which is usually what you meant.

=AVERAGEIF(D2:D11,"<>0")

Result: 1598.75

1,598.75: the average of the eight non-zero profit figures, losses included.

Blank vs zero, and the SUM/COUNTIF trap

AVERAGE and AVERAGEIF already skip empty cells, so you only need to deal with zeros. That makes a real difference to how you enter data: leave a cell empty for "no data yet", and type 0 only for a genuine zero.

A common hand-made version is SUM divided by COUNTIF "<>0". It looks right but gives a different answer, because COUNTIF with "<>0" counts the empty cell in C10 as "not zero". AVERAGEIF does not have this problem, so use it rather than building the division yourself.

=SUM(C2:C11)/COUNTIF(C2:C11,"<>0")

Result: 330

330, which is wrong: the total is divided by 8 instead of 7 because the empty May cell is counted. AVERAGEIF(C2:C11,"<>0") gives the correct 377.14.

Exclude zeros and filter by another column

AVERAGEIFS takes the range to average first, then pairs of ranges and criteria. Use one pair for the group you want and another on the same value column for "not zero".

York's five months are 0, 0, 310, 362 and 298. AVERAGEIFS with only the shop condition gives 194, which makes the shop look far weaker than it is.

=AVERAGEIFS(C2:C11,B2:B11,"York",C2:C11,"<>0")

Result: 323.33333333

323.33 units for York in the months it was open.

When every value is zero: #DIV/0!

If no cell passes the test, there is nothing to divide by and AVERAGEIF returns #DIV/0!. That happens for York in February, or for a new product with no sales yet. Wrap the formula in IFERROR to show a message or a blank instead.

Return "" for a clean report, or a word like "No sales" so readers know the gap is real. Avoid returning 0, because that zero can end up inside another average.

=IFERROR(AVERAGEIFS(C2:C11,B2:B11,"York",A2:A11,"Feb",C2:C11,">0"),"No sales")

Result: No sales

No sales: York sold nothing in February, so the average would be #DIV/0! without IFERROR.

Cells that are not next to each other

AVERAGEIF only accepts one rectangular range, so =AVERAGEIF((C3,C5,C7),">0") does not work. For a few scattered cells, add them up and divide by how many are not zero. Each comparison such as C3<>0 is TRUE or FALSE, and Excel treats TRUE as 1 when you add them.

An empty cell counts as 0 in these comparisons, so blanks are left out as well. In Excel 365 and 2021, =AVERAGE(FILTER(C2:C11,C2:C11<>0)) is another way to write the whole-column version.

=SUM(C3,C5,C7)/((C3<>0)+(C5<>0)+(C7<>0))

Result: 310

310: York's January to March units, where only March is not zero. If all three were zero you would get #DIV/0!, so wrap it in IFERROR too.

Common mistakes

Using ">0" on data that can be negative

It drops the negatives along with the zeros. Use "<>0" to remove only the zeros.

Typing 0 for "no data yet"

Leave the cell empty. AVERAGE already ignores empty cells, so you will not need a special formula.

Dividing SUM by COUNTIF "<>0"

COUNTIF "<>0" counts blank cells too, so the divisor is too big. Use AVERAGEIF(range,"<>0").

Zeros that are really text

AVERAGE and AVERAGEIF skip numbers stored as text, so a pasted column of text numbers gives a wrong average or #DIV/0!. Convert the column to numbers first.

FAQ

How do I average a range and ignore zeros in Excel?

Use =AVERAGEIF(range,">0") for values that are never negative, or =AVERAGEIF(range,"<>0") when negatives are possible. Both skip empty cells too.

Does AVERAGE ignore blank cells in Excel?

Yes. AVERAGE skips empty cells and text, but it includes zeros. That is why a 0 typed for "no data" pulls the average down while an empty cell does not.

How do I average without zeros and with another condition?

Use AVERAGEIFS: =AVERAGEIFS(C2:C11,B2:B11,"York",C2:C11,"<>0"). The first range is what you average, and the value column appears again with "<>0".

Why does AVERAGEIF return #DIV/0!?

No cell met the criteria, so there is nothing to average. Wrap it: =IFERROR(AVERAGEIF(range,">0"),"").

Functions used

Related guides

Updated November 2, 2026