Count Cells that are Non Blank (Non-Empty)

There are two primary ways to count the non-blank (non-empty) or the ones with value.

These two methods are two pre-defined functions that you can use.

The first function is COUNTIF/COUNTIFS and the other is COUNTA.

Using any of these depends on your choice. In this tutorial, we will look at both the ways to do this.

COUNTIF Not Blank (Non-Blank Cells)

In the following example, we have a range (A1:A10) with a few blank cells. But now, we need to count all the cells that are not blank in this range.

You can use the following steps to write the formula:

1. First, type =COUNTIF( in cell B1 or in any of the cells where you want to get the result.
2. 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. In the example, a few cells in this range are empty.
3. Next, in the second argument, enter “<>”. This is a nonequal operator that tells Excel to count cells that are not blank. This operator is a combination of lower than and greater than operators.
4. Ultimately, enter the closing parentheses to close the function and then hit the enter key to get the result.

The moment you hit enter, you get the count of cells that are not blank. In our example, we have six non-blank cells in the range.

Note – When you define the non-blank operator in the formula, you also have the option to refer to a cell where you have this operator. See the formula below, where we used cell D1 instead of <>.

=COUNTIF(A1:A10,D1)

You can also use a formula by combining the “does not equal to” operator with a blank value.

=COUNTIF(A1:A10,"<>"&"")

COUNTA to Count Non-Empty Cells

In Excel, you can use COUNTA to get the count of cells from a range that is not empty.

COUNTA is a function that is specifically designed to count the non-blank cells.

In the following examples, we have used the same range, A1:A10, but here, we have used the COUNTA function.

The steps are simple to use in this function.

Quick Intro to COUNTA

COUNTA function counts the number of cells that are not empty in a range. It includes cells containing values, text, logical values (TRUE or FALSE), error values, and text strings that appear empty but contain a space

Problem You Might Face While Counting the Non Blank Cells

There might be a situation where you have a range of cells with a cell or multiple cells with space as a value that looks like a blank cell.

It is where both methods you have used will give you the wrong count.

Let’s retake the same example. You can see in the above snapshot that we have six non-empty cells in total.

However, when we use all the formulas to count the non-blank cells, we have got seven.

=COUNTA(A1:A10)
=COUNTIF(A1:A10,"<>")

This is because cell A3 has a space that makes it non-empty. The solution to this problem is to use COUNTIFS.

The COUNTIFS function allows you to specify multiple criteria.

For example, you can define not including any of the cells where you have a blank space.

=COUNTIFS(A1:A10,"<>"&"",A1:A10,"<>"&" ")

Now, this formula has two criteria to check before counting the cells.

The first criterion is the same: ignoring all the cells without value means blank cells.

With this criteria, Excel only considers cells with a value, right? But as we saw in the example, there are cells with a blank space, right?

That’s why we have used a second criterion with a space. This tells the formula to ignore the cells with a space. Yes, that’s it.

sample-workbook.xlsx

Notes

• Use the COUNT function instead if you want to count only cells with numeric values and exclude text or other types of data.
• COUNTA counts all non-blank entries, including zeros (0) and cells with just spaces. If you specifically want to exclude cells with blank spaces, using COUNTIF with the “<>” criterion is more accurate.

Is there any other method to count non blank cells?

Yes, there are quite a few methods for counting the non-blank cells.

One method is to use the status bar. When you select a range of cells, the status bar will appear, showing you the count of the cells with the value (which means non-empty cells).

Note – This option will not work if you have only a single non-blank cell in your selected range, making it less useful.

Apart from this, you can also use the Find and Replace option to count the non-empty cells.

To do this, open the Find and Replace dialog box, select the range from which you count the non-empty cells, and enter an asterisk in the “Find What” input bar.

Now, in the end, click on the “Find All” button, and it will show you all the cells where you have values along with the cell count.