To count cells that are not blank, in Excel, you can use the COUNTIF or COUNTIFS function depending on the data that you have in your worksheet. You need to specify the range and then the not equal operator with a blank value to get the count of the cells which are non-blank (empty).
In this tutorial, we will look at both different ways to do this.
COUNTIF Not Blank (Non-Blank Cells)
In the following example, we have a range (A1:A10) where we have a few cells are blank. But now, we need to count all the cells which are not blank from this range.
You can use the following steps to write the formula:
- First, type =COUNTIF( in cell B1 to start entering the function in the cell where you want to get the result.
- After that, in the first argument, refer to the range A1:A10. This is the range from which you want to count the non-blank cells.
- Next, in the second argument, enter “<>”. This is a not-equal operator that tells Excel to count cells that are not blank.
- In the end, enter the closing parentheses and hit enter to the result.
The moment you hit enter you get the count of cells that are not blank. In our example, we have 6 non-blank cells in the range.
But apart from this, you can also use a formula where you can use the same operator and combine it with a blank value.
=COUNTIF(A1:A10,"<>"&"")
COUNTA to Count Non-Empty Cells
In Excel, you can use COUNTA (that you can use to a number of cells from a range that ate not empty). In the following examples, we have used the same range, A1:A10, but here we have used the COUNTA function.
The Problem You Might Face
There might be a situation where you have a range of cells where you have a cell or multiple cells with space as a value in the cell which looks like a blank cell.
In the above example, again we have the same list of values. If you see closely, you can see we have 6 cells that are not blank but, in the result, we have 7 in all three methods.
The reason for this is, that in cell A3 we have a space in the cell that makes the cell non-empty. Now, the solution to this problem is to use COUNTIFS. Just like the following example.
=COUNTIFS(A1:A10,"<>"&"",A1:A10,"<>"&" ")
Here by using the COUNTIFS function, we have created two criteria that tell Excel to count non-blank and non-space cells.