In this tutorial, you will learn to count the cells that are not blank, which means those with a value. You will learn two formulas for this and how to deal with a specific problem.
COUNTA or COUNTIF to Count Non-Blank Cells
The easiest way to count the cells which are not blank is to use the COUNTA function in Google Sheets. It’s a pre-built function in Google Sheets to count the values from a range.
You must enter the function, refer to the range, and hit enter to get the result. Yes, that’s it.
In the above example, we have six cells in the dataset A1:10, which are not blank, and that’s the count we have in the result.
But apart from this, you can also use the COUNTIF function. This function allows you to count cells with a condition. And in the criteria (condition), you can use the does not equal operator to count the non-blank cells.
To write this formula:
- First, enter COUNTIF in a cell.
- After that, refer to the range A1:A10.
- Now, in the second argument, enter “<>”.
- Next, enter an ampersand and type “ “.
- In the end, hit enter to get the result.
=COUNTIF(A1:A10,"<>"&"")Here we have used “<>” & “ “. “<>” is a doesn’t equals operator, and when you use a blank “” value along with it, this tells the COUNTIF only to consider cells with value.
A Problem You Might Face
Well, both formulas that we have discussed above are fine to use. But, there’s one unique situation where you need to use the COUNTIFS instead of COUNTIF or COUNTA.
Let’s say you have a cell in the range with a blank space. In this case, the formula will treat the cells as non-blank cells and add them to the count.
Here’s the formula that you can use:
=COUNTIFS(A1:A10,"<>"&"",A1:A10,"<>"&" ")In this formula, we have used the COUNTIFS function, where you can specify two conditions to test.
Here the first condition is to check whether a cell is blank. And the second condition is to check whether a cell has a blank space. If both conditions are valid only then a cell will consider in the count.