How to Average Without Zeros or Blanks in Excel

To average without zeros in Excel, use =AVERAGEIF(C2:C9,"<>0"). A zero that really means "no data yet" drags a normal average down, and AVERAGEIF leaves those cells out. Blank cells are a different story: AVERAGE already skips them. This guide shows both cases on one sales table, how to add a second condition, and how to stop #DIV/0! errors.

Why zeros pull your average down#

AVERAGE adds the numbers and divides by how many there are. A zero adds nothing to the total but still counts as one value, so the average shrinks.

Here is the sales table used in this guide, in A1:C9. Ben and Eve show 0 because their figures haven't come in, and Finn's cell is empty:

=AVERAGE(C2:C9) returns 295.71. The total is 2,070, and AVERAGE divides it by 7: the five real figures plus the two zeros. Finn's blank cell isn't counted. The real average of the reps who reported is 2,070 / 5 = 414.

How to average without zeros using AVERAGEIF#

Give AVERAGEIF the range and the criteria "<>0", which means "not equal to zero".

=AVERAGEIF(C2:C9,"<>0")

The result is 414. AVERAGEIF checks each cell against the criteria, keeps the ones that pass, and averages only those. The <> operator means "not equal to" in Excel, and it goes inside the double quotes together with the 0.

The full syntax has an optional third argument:

=AVERAGEIF(range, criteria, [average_range])

When you leave out average_range, Excel averages the same cells it checks. The AVERAGEIF guide covers the other criteria you can use, and the AVERAGEIF, MAXIFS and MINIFS lesson lets you practise them.

Does AVERAGE ignore blank cells?#

Yes. AVERAGE, AVERAGEIF and AVERAGEIFS all ignore empty cells and cells that contain text. You don't need a special formula to skip blanks.

Be careful with cells that only look empty. A cell formatted to hide zeros still holds 0 and still counts. The AVERAGE function guide lists the rules, and the article on AVERAGE, MIN and MAX explains the basics.

How to ignore zeros and negative numbers#

Use ">0" instead of "<>0" when only positive numbers are real data.

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

On this table the result is still 414, because there are no negative values. On data with refunds or corrections, ">0" leaves those out too. Choose based on what your negative numbers mean: if a refund of -50 is real, keep "<>0".

How to average without zeros with another condition#

Use AVERAGEIFS, which takes several pairs of range and criteria. Note that the range to average comes first.

=AVERAGEIFS(C2:C9,B2:B9,"East",C2:C9,"<>0")

This averages the East sales that aren't zero: Ana, Cara and Gia. The result is (420 + 380 + 460) / 3 = 420. Without the zero test, Eve's 0 would pull the East average down to 315.

The AVERAGEIFS function guide explains the argument order. The same "range first" pattern applies to SUMIFS and COUNTIFS.

How to handle #DIV/0! when every value is zero#

If no cell passes the criteria, AVERAGEIF has nothing to divide by and returns #DIV/0!. This can happen at the start of a month, when every figure is still 0.

=IFERROR(AVERAGEIF(C2:C9,"<>0"),"")

IFERROR shows an empty cell instead of the error. You could show a message instead, such as "No data yet". Use IFERROR only around formulas you've tested, because it hides every kind of error, including typing mistakes.

Should missing data be a zero or a blank?#

The cleanest fix is often in the data, not the formula. A zero should mean "the answer is zero": a rep who sold nothing. A blank should mean "we don't know yet". If your sheet follows that rule, a plain AVERAGE gives the right answer, because it skips blanks and keeps real zeros.

Trouble starts when one sheet mixes the two meanings. In the table above, Ben and Eve show 0 only because their figures are late. If Ben really sold nothing this month, leaving him out of the average would make the team look better than it is.

So before you reach for "<>0", ask what each zero means:

• Real result of zero: keep it. Use AVERAGE.
• Placeholder for missing data: leave it out with AVERAGEIF, or better, clear the cell so it's blank.
• A formula that returns 0 when its inputs are empty: change it to return "", for example =IF(B2="","",B2*C2). AVERAGE skips the empty text.

Other ways to average without zeros#

You may see these alternatives in older workbooks:

• SUM divided by COUNTIF: =SUM(C2:C9)/COUNTIF(C2:C9,">0") returns 414 here. Be careful: COUNTIF(range,"<>0") also counts blank cells, so with Finn's empty cell it would divide by 6 and give 345. Use ">0" for positive data, or just use AVERAGEIF.
• Median without zeros: in Excel 2021 or Microsoft 365, =MEDIAN(FILTER(C2:C9,C2:C9<>0)) returns the middle non-zero value, 420. The comparison of AVERAGE vs MEDIAN explains when the median is the better summary.

If some values matter more than others, such as prices weighted by quantity, a plain average is the wrong tool; see how to calculate a weighted average. Leaving out empty listings is also a common task in property reports, covered in Excel skills for real estate.

Practise it#

Conditional averages come up in most Excel tests, and the zero trap is a favourite trick question. Practise AVERAGEIF, MAXIFS and MINIFS in a live spreadsheet: you type the formula and it checks it instantly. If you want to warm up first, practise AVERAGE, MIN and MAX on a simpler table.