Using COUNTIF to Count Non-Blank Cells in Google Sheets

Counting non-blank cells in a spreadsheet is a fairly common affair at every level of the profession. There are many ways to go about it. But today, we will be mainly focusing on the COUNTIF function to count cells that are not blank in Google Sheets.

Let’s get started.

Using COUNTIF to Count Non-Blank Cells in Google Sheets

To show our process, we will be using the following dataset:

Here, we will be counting all the non-blank cells in the Product Name column. It contains the different types of values that we are looking for.

Since we are not going to be counting any specific text or numbers, but any value, we have to utilize something called a wildcard as our criterion.

The first wildcard we are going to be using is the asterisk (*). Our formula:

This version of the COUNTIF formula does not take into account any numerical inputs, making it only useful to count only text values.

If we want to include numerical values in our count, we can replace the asterisk with another symbol, the not-equal-to (<>). This means that it will count all cells that are not equal to blank.

Our formula:

We can even modify our asterisk COUNTIF formula by adding another function, the COUNT function, to also include numerical values:

The COUNT function specifically counts numerical values.

Let’s add some white spaces in one of our cells and use the not-equal-to COUNTIF function again:

Even though we have 5 visible values in the column, our count is showing 6. This is because the COUNTIF formula we have applied also counts:

1. White Spaces
2. Apostrophe (‘)

To remedy this, if you have a small amount of data, as we do, you can utilize Google Sheets’ Trim whitespace function to remove any known or unknown whitespaces you might have in your data selection.

Data tab > Data cleanup > Trim whitespace

But that still doesn’t remove any apostrophes (‘)

Thus, we need something more universal. And we can do that with a few modifications to our COUNTIF formula.

Formula Breakdown:

• B3:B11: Our range of data.
• “>0”: Serves to count any value greater than 0.
• “*”: Counts all cells with characters
• &: Binds the two conditions, the comparison operator and the asterisk (*)

Let’s update the formula to include numbers:

Alternatives to Count Non-Blank Cells

1. Using the COUNTA Function

COUNTA is the core spreadsheet function used to count cells that are not blank in both Excel and Google Sheets.

Its application of this function is pretty straightforward:

• value1: The range or value that will be considered when counting.
• [value2, …]: Optional. Other ranges or values to consider

Using COUNTA in our dataset:

The COUNTA also counts the following to give the wrong result:

1. Space character or whitespace
2. Apostrophe (‘)
3. Null/empty string (=””)

You can remove the whitespaces traditionally using the Trim whitespace function of Google Sheets.

Data tab > Data cleanup > Trim whitespace

2. Using a Combination of Functions – Best Way to Count Non-Empty Cells

Another way to count non-blank cells in Google Sheets is by using the SUMPRODUCT function in combination with the LEN and TRIM functions.

The formula is straightforward:

Formula Breakdown:

• The TRIM function exists to remove any whitespaces in our range.
• The LEN function condition “>0” helps to take into account all non-empty cells in our range.
• The SUMPRODUCT function calculates all the non-empty cells determined by LEN.

An Easy Way to Determine the Presence of an Invisible Value

Once we select a range of cells, we can easily see the number of cells that contain some form of value in the bottom right corner of the Google Sheets window:

So, even if you correctly use the formula to count the desired values, this tab will help you determine and root out any invisible values that may exist in your selection of cells.

This tab not only counts non-blank cells, but it also does much more:

Final Words

Counting cells that are not blank with the COUNTIF function is easy in Google Sheets. Hopefully, all the processes and tips that we have discussed in this article come in handy for you.

Feel free to comment on any other queries and advice you might have below.

Related Articles for Reading

• COUNTIF Contains Text in Google Sheets (4 Ways)
• Count Non-Empty Cells in Google Sheets (4 Easy Ways)