Let’s say you are a teacher in a school and 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 of cells 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)
The following example shows 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:
- First, type =COUNTIF( in cell B1 or any of the cells where you want to get the result.
- 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.
- 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.
- 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.
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.
Read Also – How to Count Highlighted Cells in Excel (by Color)
=COUNTIF(A1:A10,"<>"&"")
Read Also – Excel COUNTIF Blank / COUNTIFS Between Two Numbers / COUNTIF Not Equal To
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 an empty range.
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.
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.
Read Also – COUNTIF Less Than / COUNTIF Greater than 0 / OR Logic in COUNTIF
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.
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.
Notes
- Use the COUNT instead to count only cells with numeric values and exclude text or other data types.
- 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).
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.
Finally, click on the “Find All” button, which will show you all the cells where you have values along with the cell count.
You can also 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<>""))