Count Cells that are Not Blank (Non-Empty)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you are a teacher in a school, and you are working with data where you have student scores. However, when collecting test scores, some entries might be blank due to students being absent on the day of the test or because of errors in data entry.

Counting the number cells which are not blank in the “Score” column helps the teacher identify the students who were present in the school on that day.

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 A2:B21. 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,"<>"&"")
countif-with-non-equal-operator-result

Using COUNTIFS to Count Cells that are Non Blank

Let’s say you have the same data, but here, you also have columns showing the student’s gender. Now, you need to count the cells that are not blank (non-empty) based on two conditions.

When you enter this formula, it goes through column B, checks each cell to see if it has the value “Female” and if the value is there, and then adds one to the count. Then, it goes to column C to check how many cells are there that are not blank.

In the end, you get the total number of female students whose scores are there in the data.

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 example, we have used the same range, A1:A10, but here, we have used the COUNTA function.

counta-to-count-non-empty-cells

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.

cells-range-with-space-as-value

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,"<>"&" ")
countifs-formula

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 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.

And, you can use the SUMPRODUCT function to count the cells that are not blank in a list of values. Below are the formulas that you can use.

=SUMPRODUCT(--(C2:C21<>""))
=SUMPRODUCT((B2:B21="Female") * (C2:C21<>""))
Last Updated: May 03, 2024

Leave a Comment