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:

**=SUMPRODUCT((TRIM(B2:B7)<>"")*1)**

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