In this article:

How to Count Non-Empty Cells in Google Sheets

When using Google Sheets, there may be times where you have cells with no data. In these cases, you may want to count the number of cells that are not blank. This is possible by using Google Sheet COUNT functions!

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 COUNTIF to resolve this issue.

If you do not know how to use these functions, this tutorial is for you.

COUNTA Method

In Google Sheets, the COUNTA function counts all the number of cells within a range that have values or a text string in it. It ignores cells that are empty while completing the count. 

As an example, here is data that has blank cells within a range:

Google Sheet table with Customer Name and Total Ordered Amount. Several blank rows are interspersed throughout

To count the number of cells that are not blank, just follow these steps.

  1. Select a blank cell and type the =COUNTA function including the range of cells that you want to count. For example, we used =COUNTA(A2:A11).
  2. Just hit enter, and the COUNTA function will automatically count the cells that are not blank.
  3. You now have the total number of cells that have values in it!
Same table as above, but now with a circled cell counting the non-blank cells in Column A and the function circled

SUMPRODUCT Method

COUNTA function is very useful in counting cells that are blank or have no data in it. However, there may be times that COUNTA counts cells that seem empty. This is because, technically, those cells are not blank but just have a white space appearing on it. It happens, for instance, when you use conditional formatting that makes it appear as blank or sneaking a function =”” in it!

Same table as above except with new cell in Column B counting non-blanks (but having the wrong amount due to the COUNTA function)

To avoid this from happening, use SUMPRODUCT instead by following these steps:

  1. Click on an empty cell and input the function =SUMPRODUCT(--(LEN(range)>0)) to count the cells that do not appear empty. In the process, the LEN function returns a value that is greater than zero while counting the number of characters that appear in the sheet.
  2. Just click Enter and the number of the cells that have values in it will automatically appear.
Same table as above, but with a correct non-blank count for Column B using the SUMPRODUCT function

COUNTIF Method

If you have read the previous tutorials, Google Sheets hide some special characters from view because of their special functions: this include the apostrophe, that it uses to indicate that the number characters should be interpreted as a string (we used this to input phone numbers to Google Sheets). To solve this, we will learn how to use COUNTIF to count cells that are not blank and do not have special characters hidden.

As an example, let us look at this table:

Google Sheets table with only 9 entries entered


How many cells have entries in the table? There are nine (9) entries in that table. However, if you apply COUNTA function to count the entries, the result would be thirteen (13):

Google Sheets table with only 9 entries entered, but COUNTA showing 13


The reason is that there are stray apostrophes and spaces in that table, and COUNTA counted them in. To exclude them from the count, we apply the COUNTIF function with a special attribute:

=COUNTIF(B3:E7,">0"&"*")

What happened? The “>0”&”*” serves as the condition to filter out cells that contain hidden characters, thus only the ones with letters and numbers are counted. 

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.


Related Functions:

IF: Allows you to check for specific conditions across a dataset

SUMIF: Use to sum numbers if they meet a certain condition..

SUMIFS: Sums data from cells that meet multiple criteria

COUNTIF: count data if it fulfils certain criteria

COUNTIFS: Count data that fulfils two or more criteria.

IFS: Allows you to combine many if functions in one statement

IFERROR: Replaces formula error messages with specified text or a blank cell. 

IF THEN: Evaluates data against a condition and take a corresponding action when the result is TRUE. 

IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.

IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.

Multiple IF: Learn how to use multiple if statements in a single formula


IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.

IF Contains: Returns cells that contain a particular text. 

AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria. 

Use our software on expiration date management to easily set up custom reminders from your spreadsheet in just a few clicks. 

If you want to learn how to trigger a new row in Google Sheets, we also suggest checking out our detailed guide. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started