How to Count Non-Empty Cells in Google Sheets (Count Cells If Not Blank)

In this tutorial, I will be teaching you how you can count non-empty cells in Google Sheets. Knowing how to do this is particularly necessary if you have several empty rows and cells incorporated in your set of data and, you want to how many cells have content in them.

Hi, there! Welcome to another tutorial from TeqTog!

I’ll show you the 3 easy methods of “count cells if not blank” with different function. These three functions are:

  1. COUNTA Function
  2. SUMPRODUCT Function
  3. COUNTIF Function

Here we go then!

Count Non-Empty Cells in Google Sheets Using COUNTA Function

The COUTNA function in Google Sheets helps you count all of the cells which contain a text string or a numerical value in them.

Let me give you an instance below:

Count Non-Empty Cells in Google Sheets Using COUNTA Function

Suppose I want to count all of the cells in this data which are not empty.

Select the cell where I want my results and then, enter the following formula for the COUTNA function:

=COUNTA(Value 1, Value 2)

For the augment for the values, I will give the range of cells which has my data.

So,

=COUNTA(A1:A14)
COUTNA function With Value
Results of using COUTNA function for count not empty cells

Points to Note while using the COUNTA Function

While in most cases, the COUNTA function in Google Sheets does give the accurate outputs; there are a few instances where it may not do the same. Some of the reasons would be:

  1. When a cell contains a space character
  2. If a cell has an empty string (or null) value in it (=””)
  3. When a cell has an apostrophe (‘) in it.

An empty string in a cell may occur as a result of a formula. Moreover, most people use the apostrophe when entering numbers so that the numbers appear as texts

Basically, in all of the mentioned instances, the cells in Google Sheets do appear to be blank but, the COUNTA function considers them as cells with content and counts them in its calculation.

Here I have the same set of data that I have shown you in my earlier example; however this time, cell A4 has an empty string and, cell A8 contains an apostrophe.

How to Count Non-Empty Cells in Google Sheets.gif

So, as you can see, my spreadsheet has eight cells which have content. However, since there is an empty string and an apostrophe in cell A4 and cell A8, the COUNTA function has considered them content and included them in the count – thus making the result ten instead of eight.

I would suggest that you use the COUNTA function, only when you are absolutely sure that you have not given any input for an empty string or an apostrophe in any of the cells in your data.

Count Non-Empty Cells in Google Sheets Using SUMPRODUCT Function

Using the SUMPRODUCT function in Google Sheets allows you to overcome the problems of having cells with an empty string or an apostrophe being counted.

With the same example from my earlier, I use the data that had one empty string (null strings) and one apostrophe in it; to use the SUMPRODUCT function to count all the non-blank cells.

The syntax for the SUMPRODUCT function formula is:

=SUMPRODUCT(LEN(TRIM(A1:A14))>0)

If I give this formula, I will get the correct results.

How to Count Non-Empty Cells in Google Sheets Using SUMPRODUCT Function
Count Non-Empty Cells in Google Sheets Using SUMPRODUCT Function

How the SUMPRODUCT Function Works

  1. The LEN function is a part of the SUMPRODUCT function. The LEN function ensures that no null strings and apostrophes are considered. This is done by only counting those cells whose content characters are more than zero.
  2. The TRIM is another function combined with the SUMPRODUCT function. The TRIM function ensures that, there are no space characters considered in the count.

In case there is an error value in the cell, the formula will return as “error”.

How to Count Non-Empty Cells in Google Sheets Using COUNTIF Function

You can also use the COUNTIF function to count cells which are not blank. The COUNTIF function works by meeting a specific condition.

The syntax for the COUNTIF function formula is as given below:

=COUNTIF($A$1:$A$14,"?*")+COUNT($A$1:$A$14)
How to Count Non-Empty Cells in Google Sheets Using COUNTIF Function
Count Non-Empty Cells in Google Sheets Using COUNTIF Function

Just like the SUMPRODUCT function, the COUNTIF function also does not consider the cells with an empty string, apostrophe or a space character in them.

In addition to these, the COUNTIF function also ignores to count any logical values (TRUE or FALSE values) and, “Errors”.

You can read our this article to know more about COUNTIF function on How to Search IF Cell Contains in Google Sheets

Shortcut in Counting Non-Blank Cells

There is also a very simple way to view the number of cells with content. Just select all of the cells and see the count displayed on the taskbar at the bottom-right of your spreadsheet.

Shortcut in Counting Non-Blank Cells
  • The cell count displayed on the taskbar only shows the number of non-blank cells if the content of those non-blank cells are texts. If the content is numerical values then the taskbar will display the sum of those numbers instead of the cell count. You can still see the cell count by clicking on it.
  • This cell count will consider the cells with space characters, apostrophes or empty strings.

This pretty much wraps up how you can count non-empty cells in Google Sheets.

To know how you can count cells with specific texts and count cells with the same color in Google Sheets.

You may also check out our “Conditional Formatting Based on Another Cell in Google Sheets” and “How to Speed up Google Sheets (10 Easy Ways)

Best of luck!

Was this article helpful?
YesNo


Related More Articles

Leave a Comment