🚀 Quick Answer
An easy way to count non-empty cells in Google Sheets
1. Use the COUNTA function
2. Enter =COUNTA(data range)
When working with Google Sheets, occasionally we might want to find the number of cells that contains data in a column, in other words, non-empty cells.
Google Sheets has a number of ways to help us do this. The most common one being the COUNTIF function.
Here, we are going to learn how to use the COUNTIF function to count cells with a value in them; and also some other functions to count non-empty cells in Google Sheets. Moreover, we will learn how to exclude cells that contain a character but are not required in counting the tally, and thus remove false positives from the results.
Using the COUNTIF function to count non-empty cells in Google Sheets
The COUNTIF function returns the count of entries based on some condition. It counts all cells in a range that matches a single condition or multiple conditions. It can be used to find the occurrence of specific text or number; or counts cells that contain at least one value–which is what we are going to do.
To do so, we need to specify the condition for the COUNTIF function in the formula. The condition, in this case, is to count all cells that contain a value, so we will use the not equal to operator.
The formula is:
=COUNTIF(range,”<>”)The not equal to operator (“<>”) tells the formula to count cells not equal to nothing–count cells that contain at least something, in other words.
You can also make a copy of this spreadsheet to get a hands-on experience.
Suppose you want to count the number of employees present on a certain day, you can use the COUNTIF function to do so. Consider 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 on that particular day.
Using the COUNTA function to count the 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 texts as well as numbers.
Here’s how you can use the COUNTA function to count non-empty cells in Google Sheets:
- Select the values
The formula for 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 within a range or range name the function SUMPRODUCT have used along with the LEN function. The LEN function finds the number of characters in each cell in the range and returns the result. Here, we want it to count cells with more than one character, ie, not empty.
So we use the following formula:
=SUMPRODUCT(LEN(A3:A20)>0)
Count only those non-empty cells that contain a useful data
The above formulas are fine in most cases. However, in certain cases, spreadsheet data may contain seemingly empty cells that have characters in them, such as spaces and an apostrophe which are counted, thus returning a false positive answer. This is exactly what would have happened with the examples above, if we added those characters in the empty cells.
Here is the result of the COUNTA function in the above example with space and apostrophe inserted in the empty cells.
In order to remove this anomaly, we can use the TRIM function nested in the SUMPRODUCT function. The TRIM function removes space characters from texts.
The formula is:
=SUMPRODUCT(--LEN(TRIM(A3:A20))>0)
This will only count those cells that contain characters excluding spaces and apostrophes that appear blank but are not empty.
Conclusion
Counting non-empty cells in Google Sheets can be a pretty handy way to find the total number of entries in a given set of data. This can be easily achieved using a number of different functions.
When to use what:
Small data set → COUNTA, COUNTIF
Large data set → SUMPRODUCT+LEN+TRIM
Which of these to use would depend on the complexity of the data set you’re working with and whether you are certain of discrepancies in the data, such as the presence of spaces or cells with #ERROR! OR #NA values. If you’re working with a small data set, a simple formula such as the COUNTA function should do the job well, and even if it returns an incorrect result you can manually crosscheck it. However for large data set, the SUMPRODUCT function with the LEN and the TRIM function nested may be advisable.
As a general rule, simple functions for simple data; 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.
See also
How to Hide Zero-values in Google Sheets – 5 Min Easy Guide
How to use IF function across multiple sheets in Google Sheets