There are a lot of situations where you end up needing to count the number of cells in your Google Sheet that are not blank.
It could be to check how thorough survey responses were or to figure out how many shoppers didn’t include their zip code while signing up.
If you find yourself with a Google Sheet with cells that are empty (i.e. they have no data), you can use the COUNTA or COUNTIF function to count the cells that are not empty. However, this function doesn't catch hidden special characters or whitespace, so you can use the SUMPRODUCT function instead in those tricky situations.
Let’s take a look at how to use these functions in this tutorial!
The COUNTA Function
COUNTA counts all of the cells in a range that have any value in them, including those which appear more than once and text values (pro tip: it also counts whitespace and zero-length strings). So any cell that has any value inside is counted whether it’s a text value or a numerical value. To count only numeric values, you can use COUNT.
Let’s see how it works on this sample data set:
Now let’s try the following two steps:
- Click on a blank cell and type “=COUNTA.” Include the range of cells that you want to count… in the example dataset above we will use =COUNTA(A2:C4).
- Click enter and you’ll see the non-blank cell count that Google Sheets has calculated appear in the cell as you see below.
Now if we use COUNT to only count the non-empty numerical cells, then we would get 4 because there are only four numerical values in this data set.
The COUNTIF Function
Another way to get the count of blank cells is to use the COUNTIF function. We can get the same count value by entering the formula =COUNTIF(A2:C4,"<>")
COUNTIF takes a range and then a criterion (or a condition that the range has to satisfy if it has to be counted). The criterion we’ve entered - "<>" - only contains <> which means not equal to and then nothing after the not equal to operator. This means that COUNTIF will only count a cell if it is not equal to nothing.
Issues with COUNTA and COUNTIF
There are situations where you might want to count cells as blank, but COUNTA and COUNTIF don’t count it as blank.
This could be in a situation as simple as a cell that only contains whitespace to more tricky situations where you have a function like =”” (an empty string). If you’ve used conditional formatting to hide values visually (make all “0” values white), then you will end up getting a value from COUNTA that doesn’t match what you see. You might have added an apostrophe (‘) to make sure an item number like 321-323 isn’t processed as an equation (=’321-323 will make sure you don’t end up doing the subtraction of 321 minus 323).
Here’s a dataset where both COUNTA and COUNTIF don’t give a count that would match the count you would get by visually counting non-empty cells:
The Under The Hood Value and Length columns show us why COUNTIF and COUNTA are giving us a count of 6 instead of 3.
SUMPRODUCT to not count special characters
To get around these edge cases, we can use the following steps using SUMPRODUCT:
- Select an empty cell
- Type in the function =SUMPRODUCT((A2:C4<>"")*1).
- Click enter!
Let’s take a closer look at what’s happening here.
- First we check if cells are not empty using the expression A2:C4<>"". Formulas that return nothing aren’t counted by this expression, but any text value (include whitespaces) and numerical values are counted. This expression returns the boolean values TRUE and FALSE
- SUMPRODUCT can’t operate on boolean values so we multiply the result of the previous expression by 1.
- SUMPRODUCT then goes through the entire range and sums together the values. Since only the non-empty cells will have 1s, the sum will be the total number of cells that are not empty and don’t include functions that return nothing.
This still does count the cells with whitespace so we aren’t at a count of 3 that we would expect when visually inspecting the dataset. Let’s tackle the whitespace!
SUMPRODUCT to not count special characters or whitespace
We’re going to add the TRIM formula to remove cells that only contain whitespace from our count:
TRIM removes any leading or trailing whitespace from all cells in the specified range. This means that the cells that only contain whitespace will now be empty.
Counting empty cells again and again? Use Intersect
If you’re having to do the same operations like counting empty cells or doing operations with Google Sheets or Excel on a regular basis, you should use Intersect.
You can set up the data transformation steps your data should go without using complicated formulas and set them to run automatically on a regular schedule.