Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets

Google Sheets has some great formulas that allow you to quickly get information about a dataset.

One of the common things a lot of people need to do often is to count cells that are not blank in a dataset in Google Sheets (i.e., count non-empty cells).

While it can be done manually if you have a small dataset, for large ones, it’s best to leave this to the awesome COUNT formulas in Google Sheets.

And again, there are multiple ways to skin this cat.

In this tutorial, I will show you two really simple formulas to count cells if not blank in Google Sheets.

Count Cells If Not Blank Using COUNTA Function

Google Sheets has a COUNTA function that counts all the non-empty cells in a dataset (i.e., all the cells that have any text string or number in it).

Suppose you a dataset as shown below and you want to count the non-empty cells in it.

Dataset in which you want to count cells if not blank

You can use the below COUNTA formula to count all the non-empty cells in the dataset:

=COUNTA(A2:A13)

Using COUNTA function to count non empty cells

While COUNTA formula gives the right result, in this case, it may give you a wrong result in case there is a:

  • Space character
  • Null/empty string (=””).
  • Apostrophe (‘)
An empty string may be a result of a formula and a lot of people use apostrophe while entering numbers (to show these numbers as text)

In all the above cases, it may look like the cells are empty, but the COUNTA function would still consider these as non-blank and count these in the result.

Below is an example where I have an empty string (=””) in cell A4 and an apostrophe in cell A8. You can see that the result of the COUNTA formula is 11 (although only nine cells are filled with names – or at least that’s what appears to a naked eye).

COUNTA formula counting empty string and apostrophe

In case you’re sure your data doesn’t have empty strings or apostrophe, you can use the above COUNTA formula, but in case there is a possibility of it, it’s better to use a combination of formulas to get this done (covered in the next section).

Count Non-Empty Cells Using SUMPRODUCT Function

It’s wonderful how easily you can solve complex problems with a simple combination of formulas in Google Sheets.

When it comes to counting non-empty cells in a dataset, there could be cells that have null string or spaces or apostrophe.

While you can’t completely rely on the COUNTA formula, here is a simple SUMPRODUCT formula that will give you the right result in all the scenarios:

=SUMPRODUCT(LEN(TRIM(A2:A13))>0)

SUMPRODUCT formula to count cells if not blank

The above formula checks whether the cell has at least one character/number in it or not. This is checked by the LEN function.

If the length of the characters in the cell is more than 0, it’s counted, else it’s not counted.

This takes care of two scenarios – null strings and apostrophe.

And the TRIM part of the function makes sure that if there are space characters in the cells, these are ignored as well.

In case you have error values in the cells, then this formula will give you an error.

[Quick TIP] Get Count Value in the Task Bar

If you quickly want to quickly get the count of non-blank cells, you can get that from the taskbar.

All you need to do is select the cells in which you want to get the count of cells that are not blank and see the COUNT value in the taskbar (in the bottom-right part of the Google Sheets document).

COUNT value in the taskbar in Google Sheets

In case there are numbers in your dataset, by default the taskbar will show you the SUM and not the COUNT. In that case, simply click on it and it will show you the COUNT (along with other data such as Average or Max/Min).

More Statistics value about the dataset in the taskbar

Note that it will include all the cells that have anything in it – be it an empty string (=””), apostrophe (‘) or space character.

These are the three ways you can use to quickly get the count of cells if not blank in Google Sheets.

You may also like the following Google Sheets tips and tutorials:

Leave a Comment