Count If Cell Is Not Blank (Also Count If Cell Is Blank)

This is going to be a super simple post, here our aim is to find the number of cells that contain data, cells that are blank or empty should be excluded. The task is quite simple but the question becomes ‘how to do it?’. You know you want Excel to count a certain type of cells for you but if you find yourself wondering what to put in your sheets to get that count, you’re about to find out.

This tutorial will walk you through the usage of functions to count non-blank cells and at the end, we will also see a few functions to selectively count only the blank cells. So, without further ado, let's jump right in.

Count If Cell Is Not Blank

Counting Non-Blank Cells In Excel

Here we are going to talk about three Excel functions that can help us to count non-blank cells from a range.

Using COUNTIF Function

The COUNTIF function counts the number of cells within a range that meet the given criteria. COUNTIF asks for the range from which it needs to count and the criteria according to which it needs to count. To count all the non-blank cells with COUNTIF we can make use of the following formula:

=COUNTIF(range,"<>")

Let's try to understand this with an example. So, we have a dataset as shown below:

CountIf-Non-Blank-Sample-Dataset-01

From this list of product discounts, we will aim to find how many products are discounted. In Excel's terminology, this means – we are finding out how many non-blank cells there are in column D (the non-blank cells represent the discounts on products and the blank cells represent products with no discount).

We can easily accomplish this using the following COUNTIF formula:

=COUNTIF(D3:D14,"<>")

And our final result looks something like this.

CountIf-Non-Blank-Cells-COUNTIF-Function-02

Here, the formula has been fed with the range of column D (D3:D14). The criteria for searching column D is "<>" which is the indicator for non-blank cells ("" for blank cells).

The COUNTIF function has counted 8 non-blank cells as the result. This tells us that in our dataset, there are 8 products on discount.

The COUNTIF function can be used for only one condition. For multiple conditions (e.g., non-blank cells and discount more than 20%), we can use the COUNTIFS function.

The next function we will use to count non-blank cells is the COUNTA function.

Using COUNTA Function

By its nature, the COUNTA function counts the cells in a range that are not empty. It is a single argument function (in its simplest form) requiring just the range from which to count non-blank cells.

We will use it in our last example and simply add our range D3:D14 to the COUNTA function in this way:

=COUNTA(D3:D14)

We have the same results as the COUNTIF function i.e., 8 non-blank cells, with fewer arguments.

CountIf-Non-Blank-Cells-COUNTA-Function-03

For the sole purpose of counting non-blank cells, the COUNTA function offers an easy, straightforward solution.

However, the COUNTA and COUNTIF functions will also count cells with space characters and formulas that return an empty string. This means that the functions will count cells that look blank but are not essentially blank.

Why is that a problem?

Technically, a space character or a formula that returns an empty string is very different from a blank cell. Although they look similar but technically they are two different things.

But since our purpose here is to count non-blank cells, so we might want the cells that appear blank to be counted as blank cells (technically, even though they are not blank). To accomplish this instead of using the COUNTIF or COUNTA functions it is better to use a SUMPRODUCT function. And this is what we are going to see in the next section.

Tip: If you are adamant about using the COUNTIF or COUNTA function for counting non-blank cells, another way around it would be to filter the data and get rid of the cells with hidden values. This will refine the results without the inclusion of faux blank cells. For now, we will move onto the SUMPRODUCT function.

Using SUMPRODUCT Function

The SUMPRODUCT returns the sum of the products of the supplied ranges or arrays. To count non-blank cells using SUMPRODUCT function we can use the below formula:

=SUMPRODUCT(--(C2:C13<>""))

Let's try to understand the formula first and then we can compare it with the COUNTIF and COUNTA functions.

In the above formula, first of all, we are checking if the values in the range C2:C13 are equal to an empty string (nothing). This returns an array of boolean values like this –

=SUMPRODUCT(--({TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE}))

Now, since the SUMPRODUCT function cannot sum boolean values we are converting them to 0's and 1's by using the double-negatives. So, the formula gets further simplified to something like this –

=SUMPRODUCT({1, 1, 1, 0, 1, 1, 1, 1, 0, 1, 1, 1})

The SUMPRODUCT  function adds all these 1's and 0's and returns the final result.

An important thing worth noting here is that the above formula does not treat space character as an empty string (nothing) so it will still be counted as a non-blank cell. To count them as blanks we can make use of the TRIM function and the final formula would look like this –

=SUMPRODUCT(--(TRIM(C2:C13)<>""))

Pro Tip: Instead of using the double negatives to convert boolean values to 1's and 0's we can also either add 0 to them or multiply them by 1 and our final result would still be the same.

Now, Let's have the COUNTIF, COUNTA, and SUMPRODUCT functions side by side and analyze them:

Comparison-Of-COUNTIF-COUNTA-SUMPRODUCT-For-Non-Blank-Cells-4

What we can see from the serial numbering, is that there are 12 rows in our dataset. C5 is a blank cell that is correctly interpreted as blank cells by COUNTIF and COUNTA. It appears that there are 2 more blank cells (C8 containing a space character and C10 containing a formula that returns an empty string i.e., ="") in the range which means that there are 9 non-blank cells.

While the COUNTIF and COUNTA functions have treated C5 as a blank cell but they treat the other two C8 and C10 as non-blank cells and thus return 11 as the count.

On the other hand, the SUMPRODUCT based formula correctly identifies all such cells and hence returns 9 as the count of non-blank cells.

Recommended Reading: Counting Unique Values In Excel

Counting Blank Cells

Now is the turn for counting blank cells. We have ahead 2 very simple ways of counting empty cells with help of the COUNTBLANK and the COUNTIF function.

Using COUNTBLANK Function

The COUNTBLANK function, pretty self-explanatory, counts the number of empty/blank cells in a specified range. We will use the function plainly to get our results:

=COUNTBLANK(D3:D14)

COUNTBLANK-FUNCTION-Count-Blank-Cells

We have inserted the range D3:D14 in the formula to see how many blank cells there are in column D which will tell us the number of products with no discount. The outcome of the COUNTBLANK function is "4" blank cells.

Using COUNTIF Function

As explained above, the COUNTIF function can be used for counting both, blank and non-blank cells. Now we will see how to use COUNTIF for counting blank cells.

To count blank cells the COUNTIF function can be used as:

=COUNTIF(D3:D14,"")

COUNTIF-Function-To-Count-Blank-Cells

In the formula, which is made up of the range and criteria, we have swapped the criteria for counting non-blank cells (i.e., "<>") with the criteria for counting blank cells (i.e., ""). With the range specified as D3:D14, the COUNTIF function returns "4" blank cells as the result, indicating 4 undiscounted products.

At the end of this tutorial, we hope you won't feel blank when trying to get Excel to count blank and non-blank cells. We're following up with more how-to's as you read!

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.