ExcelInsider Team
When you work with big datasets in Google Sheets, you might need to count only the cells that have data in them and leave out the empty ones. Counting non-empty cells helps you focus on the entries that matter, whether you’re keeping track of attendance, checking off tasks that have been done, or looking at filled-out responses in a form. Users often look for this solution when they want to avoid mistakes in calculations caused by empty cells or when they want to quickly summarize filled data points.
To count non-empty cells with COUNTA function in Google Sheets, follow the steps below:
➤ Select the empty cell, F2, where you want to display the count.
➤ Enter the formula:
=COUNTA(C2:C11)
➤ Press Enter to see the total number of non-empty cells.
We’ll go over a few useful ways to count cells that aren’t empty in Google Sheets in this article. We’ll talk about basic formulas like COUNTA, and more advanced counting methods like COUNTIFS, and ARRAYFORMULA. You can use the method that works best for your task because each one works best in a unique situation.
What Are Non-Empty Cells in Google Sheets?
In Google Sheets, non-empty cells are those that contain any kind of data—text, numbers, dates, or formulas. Counting these cells is a reliable way to confirm that your analysis has all the necessary data and that it is accurate.
For instance, when you add up or average numbers, including empty cells, can give you the wrong answer. It also helps you find missing data. If the number of non-empty cells doesn’t match what you expect, you know some gaps need to be filled. The function is very helpful when dealing with big datasets. Counting non-empty cells makes sure that your insights are based on real data, whether you’re cleaning it up, keeping track of changes, or making reports.
Using COUNTA Function to Count Non-Empty Cells
This is the simplest and most common way to do it. COUNTA counts all cells that aren’t empty, no matter what kind of data they contain, like text, numbers, or special characters.
We are keeping track of 10 content marketing tasks in the next dataset. There is an assignee and a current status for each task. Suppose you want to know how many tasks have a value in the Status column that isn’t empty (i.e., how many status updates have been made).
Steps:
➤ Click on F2, a blank cell, where you want to display the result.
➤ Type the following formula:
=COUNTA(D2:D11)
➤ Press Enter.
Note:
COUNTA counts any cell that has data in it, even if it’s just whitespace or invisible characters.
Using COUNTA with Multiple Ranges
This method is helpful when you need to count cells that aren’t empty and are spread out over several rows or columns. Now, you need to figure out how many values are in the Task ID and Task Description columns.
Steps:
➤ Select an empty output cell. In this case, F2.
➤ Type the formula:
=COUNTA(C2:C11, D2:D11)
➤ Press Enter.
Note:
COUNTA can work with more than one range that isn’t next to another range, as long as commas separate the ranges.
Count Only Specific Text with COUNTIF Function
You can use COUNTIF to find out how many times a certain status shows up. Here’s how to use it if you want to know how many tasks have been started.
Steps:
➤ Select F2 for the result.
➤ Type this formula:
=COUNTIF(D2:D11, “Done”)
➤ Press Enter.
Note:
This is outstanding for keeping track of progress, milestones, or reports that show what you’ve done.
Applying COUNTIF to Exclude Cells with Only Spaces
This method works when some cells look empty but are full of white space. Using COUNTIF(range, “<>”) makes sure that only cells that are not empty and have meaning are counted. The formula only counts cells that are not empty; it ignores cells that only have whitespace.
Steps:
➤ Select the result cell F2.
➤ Type the formula:
=COUNTIF(C2:C11, “<>”)
➤ Press Enter.
Note:
This is best used when checking or cleaning data to ensure it is correct. It helps keep from counting cells that look empty but aren’t.
Count Non-Empty Cells in a Specific Column Based on Multiple Conditions
This method uses COUNTIFS to count cells that are not empty and meet more than one condition. Let’s say you only want to count the tasks that Anna has been given that already have a status update.
Steps:
➤ Click on the blank cell, F2.
➤ Type the following formula:
=COUNTIFS(B2:B11, “Anna”, D2:D11, “<>”)
➤ Press Enter.
Note:
This method is useful for filtering data by team members, date, category, or other criteria.
Using ARRAYFORMULA to Count Non-Empty Cells Dynamically
ARRAYFORMULA automatically applies functions over long ranges, so new data can be added and the updates will happen right away. This is an excellent approach to make the count update automatically when you add rows to your dashboard.
Steps:
➤ Select the destination cell. Here, F2.
➤ Type this formula:
=ARRAYFORMULA(COUNTA(D2:D100))
➤ Press Enter.
Note:
You can make your reports even more flexible by using ARRAYFORMULA with other functions like IF, LEN, or REGEXMATCH.
Frequently Asked Questions
What does COUNTA count in Google Sheets?
COUNTA counts all cells that aren’t empty, even if they just have text, numbers, dates, or spaces in them. It doesn’t ignore invisible whitespace, which can be confusing if the data isn’t clean.
How do I exclude empty strings or spaces when counting?
Instead of COUNTA, use COUNTIF(range, “<>”). This makes sure that only real, visible data is counted.
Why is COUNTA giving a higher count than expected?
Look for spaces or line breaks that you can’t see in your cells. COUNTA counts everything that isn’t empty.
What is the formula if the cell is not empty in Google Sheets?
Type in the formula: To check cell D2, type =IF(ISBLANK(D2), “Cell is empty”, “Cell is not empty”). If the cell is empty, this formula will return “Cell is empty.” If it has data in it, it will return “Cell is not empty.”
Concluding Words
It’s necessary to count non-empty cells in Google Sheets for reporting, auditing, and automating workflows. Depending on your dataset and needs, COUNTA, COUNTIF, and more dynamic solutions like ARRAYFORMULA all have their own strengths. If you learn these skills, your data analysis will always be correct, clean, and up-to-date.
