How to Count Cells If Not Blank in Google Sheets

Sometimes you may have a dataset where some of the cells or rows are blank. In such a case, if you want to count cells that are not blank, then you need to rely on Google Sheets count functions.

In this tutorial, I will show you how to count cells if not blank in Google Sheets.

Count Cells If Not Blank Using COUNTA Function

COUNTA function in Google Sheets counts all the cells that have any value or text string in it, and ignores the blank cells.

For example, if you have a dataset as shown below and you want to count cells that are not blank, you can use the COUNTA function.Count Cells If Not Blank in Google Sheets - COUNTA

While the COUNTA function works well in most of the cases, it fails to give the right result when there is an empty string in the cell, or there is an apostrophe.

An empty string (“”) can be a result of a formula while apostrophe is often used by many people during data entry.

The empty string and apostrophe make the cell look blank, but are not ignored by the COUNTA function. So if you have a cell that has these, it will be counted by COUNTA function (as shown below):

Count Cells Non Blank in Google Sheets - empty string

Note that the cell A3 has an empty string (“”) and cell A7 has an apostrophe (‘). While these look empty, these cells are counted by the COUNTA function.

Hence, the result is 12, while it should be 10.

Count Cells If Not Blank Using COUNTIF function

Countif function in Google Sheets can be used to count the number of cells when a specified condition is met

To count all the cells that are not blank, you can use the following function:

=COUNTIF($A$1:$A$13,”?*”)+COUNT($A$1:$A$13)

Count Cells If Not Blank in Google Sheets - Countif

Note that this formula ignores blank cells that contain an apostrophe or empty string.

This formula would also ignore Logical Values (TRUE/FALSE) and Errors.

You can also use the below SUMPRODUCT formula for counting non-blank cells in Google Sheets:

=SUMPRODUCT(LEN(A1:A13)>0))

You May Also Like the Following Google Sheets Tutorials:

>