🚀 Quick Answer: One simple way to count non-empty cells in Google Sheets:
- Use the COUNTA function.
- Enter =COUNTA(data range).
When working with Google Sheets, there are times when you want to know the number of cells that contain data in a column, in other words, non-empty cells.
Google Sheets offers several methods to help you do this. The most common one is the COUNTIF function.
In this article, we will learn how to use the COUNTIF function to count cells containing a value, as well as other functions to count non-empty cells in Google Sheets. Additionally, we will learn how to exclude cells containing a character that should not be counted, to avoid false positives.
Using the COUNTIF Function to Count Non-Empty Cells in Google Sheets
The COUNTIF function returns the number of entries based on a condition. It counts all the cells in a range that match a single condition or multiple conditions. It can be used to find the occurrence of specific text or number, or to count cells containing at least one value, which is what we’ll do here.
To do this, we need to specify the condition in the COUNTIF function formula. The condition, in this case, is to count all cells containing a value, so we’ll use the not equal to operator.
The formula is:
=COUNTIF(range,"<>")The not equal to operator (“<>”) tells the formula to count cells different from nothing, in other words, cells containing something.
You can also make a copy of this spreadsheet for a hands-on experience.
Let’s say you want to count the number of employees present on a certain day, you can use the COUNTIF function to do that. Let’s take the example below.
Here, the range is A3:A20. So we enter the following formula in an empty cell:
=COUNTIF(A3:A20,"<>")This will return the total number of employees present that day.
Using the COUNTA Function to Count Non-Empty Cells in Google Sheets
The COUNTA function is one of the simplest methods to count non-empty cells in Google Sheets. It can be used to count both text and numbers.
Here’s how you can use the COUNTA function to count non-empty cells in Google Sheets:
- Select the values.
The formula for the COUNTA function is:
=COUNTA(value1, [value2, ...])Using the SUMPRODUCT Function to Count Non-Empty Cells in Google Sheets
To count the number of non-empty cells in Google Sheets in a range or named range, the SUMPRODUCT function is used in conjunction with the LEN function. The LEN function finds the number of characters in each cell of the range and returns the result. Here, we want it to count cells containing more than one character, in other words, cells that are not empty.
So we use the following formula:
=SUMPRODUCT(LEN(A3:A20)>0)This will only count cells containing characters, excluding spaces and apostrophes that appear empty but are not.
Only Count Non-Empty Cells with Useful Data
The formulas mentioned above work in most cases. However, in some cases, the data in the spreadsheet may contain seemingly empty cells but with characters such as spaces and apostrophes, which are counted, thereby returning a falsely positive response. This is exactly what would have happened with the examples above if we had added these characters in the empty cells.
Here’s the result of the COUNTA function in the example above with spaces and apostrophes inserted in the empty cells.
To remove this anomaly, we can use the TRIM function nested inside the SUMPRODUCT function. The TRIM function removes spaces from text.
The formula is:
=SUMPRODUCT(-LEN(TRIM(A3:A20))>0)This will only count cells containing characters, excluding spaces and apostrophes that appear empty but are not.
In conclusion
Counting non-empty cells in Google Sheets can be a convenient way to find the total number of entries in a given dataset. This can be easily achieved using several different functions.
When to use what:
- Small dataset → COUNTA, COUNTIF
- Large dataset → SUMPRODUCT+LEN+TRIM
The choice between these methods will depend on the complexity of the dataset you’re working with and the possible presence of inconsistencies in the data, such as the presence of spaces or cells with #ERROR! or #NA values. If you’re working with a small dataset, a simple formula like the COUNTA function will suffice, and even if it returns an incorrect result, you can manually verify it. However, for a large dataset, it is advisable to use the SUMPRODUCT function with the LEN and TRIM functions nested inside.
As a general rule, use simple functions for simple data, and complex functions for complex data.
We hope this article has been helpful to you. Visit our blog for more articles and tutorials on Google Sheets.
