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.

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