How to count non-empty cells in google sheets : 3 Easy methods

In this tutorial, we will show you how to count non-empty cells in Google Sheets.

While using google sheets you might come across a situation where you need to count non-empty cells ( Blank cells ) without any data. But how to do it? Let’s find out.

You can count non-empty cells using the COUNT function in Google sheets.

COUNTA is a function for counting cells that are not empty. Sometimes, however, the COUNTA function doesn’t catch hidden special characters, so we can use SUMPRODUCT or resolve this issue.

Count non-empty cells in google sheets using COUNTIF

We can use the COUNTIF function in google sheets which will count the number of cells when it meets a certain condition.

Special characters like apostrophes are hidden in google sheets as they have special functions.

How To Count Cells With Text. Excel...
How To Count Cells With Text. Excel Tip.

Hence we will use the COUNTIF function with a condition to filter out special characters and empty cells.

The following function can be used to count non-empty cells.

=COUNTIF($A$1:$A$13,">0"&"?*")+COUNT($A$1:$A$13)

In the below example you see the count for non-empty cells which is 8, using the above function

count non-empty cells in google sheets

Function COUNIF will only count numbers and letters in the non-empty cells and ignore the special characters and blank spaces because of the condition “>0″&”?*”

Count non-empty cells in google sheets using COUNTA

When you use the COUNTA function in google sheets it will count the number of cells with a data value or string while ignoring the blank cells during the count.

For example, below we have a list of items with blank cells.

Here we can count the total number of cells using the COUNTA function.

Used function =COUNTA(A1:A13).

follow the below steps :

  • Select the blank cell where you need the output.
  • Type the COUNTA function in the fx function area as shown below .
  • Now hit it enter and you will get the result in the desired cell .

Count non-empty cells in google sheets using SUMPRODUCT

COUNTA function has certain limitations like when using conditional formating it might give an incorrect result because it will also count the non-empty cells as white spaces.

To fix this issue, you can use the SUMPRODUCT function as shown below :

  1. Select the cell where you need the total count .
  2. Copy the function  =SUMPRODUCT((LEN(range)>0)) in the fx function area.
  3. Hit enter and get the desired result as shown below .

The following can be used to count non-empty cells in google sheets. Adjust the formula range as per your requirement.

=SUMPRODUCT((LEN(A2:A14)>0))

We hope this tutorial on how to count non-empty cells in google sheets was helpful. Do check out our other useful articles.

Related articles:

How to Rotate Text in Google Sheets