=AVERAGEIF(A1:A9,"<>") averages only the cells that aren’t empty. Worth knowing before you use it, though: plain =AVERAGE(A1:A9) already skips blank cells, and text cells too — it only divides by the count of actual numbers. So for a straightforward range with gaps in it, both formulas return the same answer and AVERAGE is simpler.
The AVERAGEIF version earns its place when you need to add a second condition, for example =AVERAGEIFS(B1:B9,B1:B9,"<>",A1:A9,"North"). What AVERAGE does not skip is a zero, so if the blanks in your data were filled with zeros at some point, use =AVERAGEIF(A1:A9,"<>0") instead — that’s usually the real problem behind this search.
And if every cell in the range is empty, there’s nothing to average and both formulas return #DIV/0!; wrap it in =IFERROR(AVERAGE(A1:A9),0) if that can happen.
To average only non-blank cells in Excel, you can use the AVERAGEIF function. And in the criteria, you need to use does not equal operator to refer to only non-blank cells. In this tutorial, we will look at this formula in depth. The good news is that the regular AVERAGE function also skips empty cells on its own, so =AVERAGE(A1:A9) gives the same result; AVERAGEIF just makes the condition clear in the formula.
Average Only Cells which are Not Blank
You can use the following steps:
- First, in cell C1, enter the AVERAGEIF function.
- After that, in the first argument (range), refer to the range A1:A9.
- Now, in the second argument, enter the does not equal operator (<>) using double quotation marks.
- In the end, enter the closing parenthesis and hit Enter to get the result.
=AVERAGEIF(A1:A9,"<>")
When you use it, AVERAGEIF only considers cells that are non-blank and averages only those cells. You can see when I select the cells where we have values, it shows the same average on the status bar.
A Problem You Might Face
There might be a situation where you have a cell in the range that looks blank but has an invisible space. Such a cell isn’t blank for Excel; it has text. But AVERAGEIF (and AVERAGE) only average numbers and skip text, so the space doesn’t change the result. Where it does cause a wrong average is when you count cells with COUNTA, like SUM(A1:A9)/COUNTA(A1:A9), or use AVERAGEA, as both count the space cell.
If you want to make the condition clear anyway, you can use the below formula where we have used AVERAGEIFS to specify two criteria: one is to include non-blank cells, and the other is to ignore cells with a space.
=AVERAGEIFS(A1:A9,A1:A9,"<>",A1:A9,"<>"&" ")
Average Non-Blank Cells but Exclude Zeros
Blank cells are skipped, but cells with 0 are still part of the average. If you also want to leave out zeros, use “not equal to zero” as the criteria. Here’s the formula, and you can learn more about it in average but exclude zeros:
=AVERAGEIF(A1:A9,"<>0")
AVERAGE vs AVERAGEA with Blank and Text Cells
- AVERAGE and AVERAGEIF: skip empty cells and cells with text (including a space or a number stored as text).
- AVERAGEA: skips empty cells, but counts cells with text as 0, which pulls the average down.
- SUM/COUNTA: counts every non-empty cell, including text, so it also gives a lower average.
So, for a normal average of non-blank cells, AVERAGE or AVERAGEIF is the right choice. If you see a number that doesn’t count (it’s left-aligned or has a green triangle), it’s stored as text, and you need to convert it to a number first.
Frequently Asked Questions
Does AVERAGE ignore blank cells?
Yes. AVERAGE skips empty cells and cells with text. It only includes cells with numbers, and 0 counts as a number.
Why is my average lower than expected?
Usually because the range has zeros, or because you used AVERAGEA or SUM/COUNTA, which count text cells. Use AVERAGEIF with “<>0” to leave zeros out.
How do I average non-blank cells based on another column?
Use AVERAGEIFS with the other column as the criteria range. This formula averages B1:B9 only where A1:A9 is not blank:
=AVERAGEIFS(B1:B9,A1:A9,"<>")
This does not work if all cells in the range are blank.