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:

primary worksheet for COUNTIF not blank in Google Sheets

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:

=COUNTIF(B3:B11,"*")

number not counted with base COUNTIF 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:

=COUNTIF(B3:B11,"<>")

number counted with not-equal-to symbol for COUNTIF not blank in Google Sheets

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

=COUNTIF(B3:B11,”*”)+COUNT(B3:B11)

including number with COUNT function

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:

whitespace counted with COUNTIF

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

navigating to Trim whitespace in Google Sheets

whitespace trimmed in COUNTIF not blank in Google Sheets

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

apostrophe was not removed

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

=COUNTIF(B3:B11,">0"&"*")

modifying COUNTIF to count non-blank cells in Google Sheets

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:

=COUNTIF(B3:B11,">0"&"*")+COUNT(B3:B11)

completing the formula of COUNTIF and COUNT

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:

COUNTA(value1, [value2, ...])

COUNTA syntax

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

Using COUNTA in our dataset:

=COUNTA(B3:B11)

COUNTA function counts all cells with values in Google Sheets

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:

=SUMPRODUCT(LEN(TRIM(B3:B11))>0)

SUMPRODUCT alternative to count non-blank cells in Google Sheets

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:

built-in panel to count non-blank cells in Google Sheets

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:

other options in the panel

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo