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.
In the below example you see the count for non-empty cells which is 8, using the above function
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 :
- Select the cell where you need the total count .
- Copy the function =SUMPRODUCT((LEN(range)>0)) in the fx function area.
- 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.
We hope this tutorial on how to count non-empty cells in google sheets was helpful. Do check out our other useful articles.