How to count non-blank cells in Google Sheets

Within a Google Sheet, you might have blank rows. Some of these rows could be populated with special characters that are not visible unless you click on the cell. There are multiple ways of identifying the number of cells that are not blank. Let’s go over these one by one. 

4 ways to count non-blank cells on Google Sheets 

  • Counting non-blank cells using COUNTA function 
  • Counting non-blank cells using COUNTIF function 
  • Counting non-blank cells using the SUMPRODUCT function 
  • Counting non-blank cells using Count value in the Taskbar 

Method 1: Counting non-blank cells using COUNTA function 

COUNTA function is handy when you have cells with a text string or a value. 

Step 1: Applying the function. 

Take a blank cell, which is not included in the range for which you wish to calculate the non-empty cells.

Step 2: Writing as per the specific syntax. 

Now write as per the following syntax:

= COUNTA(range of cells)

As you can see, the range here is from cells B2 to B18.

Step 3: Getting the output. 

Click Enter and you will be able to see the number of non-blank cells.

Taking another example with numerical values, we get the same result. 

The COUNTA function is great for counting non-blank cells. There is an issue with the function though on what it considers a non-blank cell. The COUNTA function considers the following cells as blank:

i) Cells that have an apostrophe

ii) Cells that have a blank space

iii) Cells that have an empty string “ “ 

Try the COUNTIF function if you feel you need to check your sheet for such cells.


Method 2: Counting non-blank cells using COUNTIF function

The COUNTIF function is great for counting non-blank cells in a data set that has special characters input in cells. As explained before, the COUNTIF function is also handy when there is an empty string within a cell. These cells generally appear blank but are not. For example, cells containingor

Such cells can result when different team members work on the same sheet. 

The COUNTA function doesn’t count cells with a space character as well. 

Here, the cells C17 and C18 have an apostrophe entered as a value. 

If we apply COUNTA here in this range, it will count these cells as non-blank as well.

Using the COUNTIF function is a way around this. 

Step 1: Applying the function. 

Take a blank cell, which is not included in the range for which you wish to calculate the non-empty cells. 

Step 2: Writing as per the specific syntax. 

We will need to change the syntax to this: 

= COUNTIF(range, “>0” & “*”) 

The condition “>0” & “*” filters out cells with hidden characters. 

Step 3: Getting the output. 

Click Enter and you will be able to see the number of non-blank cells.


Method 3: Counting non-blank cells using the SUMPRODUCT function 

We know that COUNTA can return incorrect values if there are special characters in the cells. You can use the SUMPRODUCT function as a way around this. 

Step 1: Applying the function. 

Click on an empty cell, not included in the range that you want to count non-blank cells. 

Step 2: Writing as per the specific syntax. 

Now write as per the following syntax:

= SUMPRODUCT(–(LEN(range)>0))

The LEN function here checks the cell for any character. If the character’s length is more than 0, it counts it.

Step 3: Getting the output. 

Click Enter and you will be able to see the number of non-blank cells.


Method 4: Counting non-blank cells using Count value in the Taskbar

This is by far the easiest method out of all that we have covered today. 

Step 1: Selecting cells for which you need to check the count.

Click and select the cells that you need the count for. Click on one end of the cell range and press Shift and click on the ending cell of the range. 

Step 2: Getting the correct value from the Taskbar. 

You can view the Count value on the bottom right corner of your Google Sheet. 

In case of numerical values, the Sum will be visible. Click on the value being displayed. 

Right click and you will be able to see Count.


Conclusion

These methods will prove to be useful when you work on your projects. Learn more of these hacks and to-dos on our website. 

Leave a Comment