Google Sheets is a powerful tool that offers various formulas to quickly retrieve information from a dataset. One common task you’ll often find yourself needing to do is counting non-empty cells in a dataset on Google Sheets. While you can do this manually for small datasets, it’s best to rely on the awesome counting formulas in Google Sheets for larger datasets. And once again, there are multiple ways to achieve this.
Using Google Sheets COUNTA Function to Count Non-Empty Cells
Using COUNTA is the simplest way to do it, but it may fail if there are cells that appear empty. The COUNTA function counts all non-empty cells in a dataset (i.e., cells containing text strings or numbers).
Suppose you have a dataset, as shown below, and you want to count the non-empty cells. Here’s how to count non-empty cells in Google Sheets:
Here’s the COUNTA formula I used to count all non-empty cells in the dataset:
=COUNTA(A2:A13)
Here’s what I did to perform the Google Sheets count calculation for non-empty cells:
- Select an empty cell
- Type
=COUNTA( - Select the range you want to count
- Press “Enter”
While the COUNTA formula gives the correct result in this case, it can give you an inaccurate result if there are:
- A space character
- An empty string (
="") - An apostrophe (
')
An empty string may be the result of a formula, and many people use apostrophes when entering numbers (to display those numbers as text). In all these cases, it may appear that the cells are empty, but the COUNTA function will still consider them as non-empty and count them in the result.
Here’s an example where I have an empty string (="") in cell A4 and an apostrophe in cell A8. You can see that the result of the COUNTA formula is 11 (even though only nine cells are filled with names – at least that’s what appears to the naked eye).
If you’re confident that your data doesn’t contain empty strings or apostrophes, you can use the above COUNTA formula. However, if there’s a possibility of finding them, it’s better to use a combination of formulas to achieve that (covered in the next section).
Counting Non-Empty Cells Using the SUMPRODUCT Function
Using SUMPRODUCT is a more complex method, but it’s more reliable if you’re unsure whether certain cells are actually empty or contain apostrophes.
It’s wonderful to see how you can solve complex problems with a simple combination of formulas in Google Sheets. When counting non-empty cells in a dataset, there may be cells with empty strings, spaces, or apostrophes.
While you can’t fully rely on the COUNTA formula, here’s a simple SUMPRODUCT formula I use that will give you the correct result in all scenarios:
=SUMPRODUCT(LEN(TRIM(A2:A13))>0)
Here’s how to count filled cells in Google Sheets using this formula:
- Select an empty cell
- Type
=SUMPRODUCT(LEN(TRIM( - Select the range you want to count
- Close the formula with
))>0) - Press “Enter”
Here’s how this “count if non-empty” method works on Google Sheets:
- The above formula checks if the cell contains at least one character or number. The LEN function checks for this. If the length of characters in the cell is greater than 0, it’s counted; otherwise, it’s not counted.
This takes into account two scenarios – empty strings and apostrophes.
The TRIM part of the function ensures that if space characters are present in the cells, they are also ignored.
If you have error values in the cells, this formula will give you an error.
Google Sheets COUNTIF Non-Empty
COUNTIF is often used to count data based on what’s contained in the cell (counting specific text), but you can also use it as a non-empty Google Sheets COUNTIF formula. Let’s take a look at a non-empty COUNTIF function in Google Sheets.
Here’s an example of a non-empty COUNTIF formula in a Google spreadsheet:
=COUNTIF(A2:A8,"<>")This non-empty COUNTIF formula in Google Sheets works by using the <> operator to include anything that contains any text while performing the count.
To create your own similar non-empty COUNTIF formula in Google Sheets, here’s what you need to do:
- Click on an empty cell
- Start the formula by typing
=COUNTIF( - Select the range you want to count
- Type a comma (
,) - Type
"<>"(don’t forget the quotes) - Press “Enter”
[Quick Tip] Getting the Count Value from the Taskbar
If you want to quickly count non-empty cells in Google Sheets, you can get this information from the taskbar.
Simply select the cells where you want to get the count of non-empty cells and check the COUNT value in the taskbar (bottom right of the Google Sheets document).
In case there are numbers in your dataset, by default, the taskbar will show you the SUM and not the COUNT. In this case, click on it, and it will show you the COUNT (along with other data such as average, maximum, or minimum).
Note: This will include all cells that contain anything – an empty string (=""), an apostrophe ('), or a space character.
Frequently Asked Questions
Can Google Sheets Count Non-Empty Cells?
Yes, you can use the COUNTA, COUNTIF, or SUMPRODUCT functions to count non-empty cells in Google Sheets. The simplest method among these methods in Google Sheets to count non-empty cells is COUNTA. To use it:
- Type
=COUNTA(in an empty cell - Select the range you want to count
- Press “Enter”
But you can also use COUNTIF to count non-empty cells in Google Sheets:
- Type
=COUNTIF(in an empty cell - Select the range you want to count
- Type
"<>"(don’t forget the quotes) - Press “Enter”
Conclusion
These are the four ways to count cells if they are not empty in Google Sheets. Google Sheets COUNTIF non-empty is a fairly simple and reliable method, but exploring the other methods is also a good idea to confirm the accuracy of your results.
Alternatively, if you want to enhance your Google Sheets skills, check out this comprehensive Google Sheets formulas and functions course. It covers all the basic functions, including COUNTIF and SUMIF, as well as formatting text strings and validating emails, numbers, and URLs. By the end of this two-hour course, you’ll have all the knowledge you need to master Google Sheets.
You might also enjoy the following Google Sheets tips and tutorials:
- How to count cells with specific text in Google Sheets
- How to get the number of words in Google Sheets
- How to separate first and last names in Google Sheets
- Counting characters in Google Sheets
- Multiple IF function in Google Sheets – Testing multiple conditions (examples)
- How to use the SUMIF function in Google Sheets? Examples!
- ESTNUM function in Google Sheets (Check if a cell contains a number)
