Published on January 10, 2023 by Jake Sheridan
In this tutorial, you will learn how to count non-empty cells in Google Sheets.
Methods to Count Non-Empty Cells in Google Sheets
When working with a spreadsheet in Google Sheets, you may have a table that contains empty rows or cells. If you have a spreadsheet with 1000 rows, you might want to know how many of those rows actually contain values.
In Google Sheets, there are several ways to count non-empty cells. The COUNTA function counts the number of cells in a range that are not empty. We can also use the COUNTIF function as it counts the number of cells that meet certain criteria. We can also use the SUMPRODUCT function to count non-empty cells in a range.
In this guide, we will show you how to count non-empty cells in Google Sheets using COUNTA, COUNTIF, and SUMPRODUCT. We will also show you how to count non-empty cells without using any formulas at all.
How to Count the Number of Non-Empty Cells in Google Sheets
Here’s how to count the number of non-empty cells in Google Sheets.
Step 1
First, select a new cell to place the number of non-empty cells in our target range.
In this example, we will use cell D1 to display the number of non-empty cells in the range A2:A22.
Step 2
Next, go to the Google Sheets formula bar and type “=COUNTA(” to start the COUNTA function.
The COUNTA function will return the number of supplied values in the argument. We will use COUNTA instead of COUNT as the latter only considers numeric data.
Step 3
Add the target range to count as an argument of the COUNTA formula. Press Enter to get the final result.
In this example, we determined that the range contains 17 non-empty cells.
Step 4
We can also use the COUNTIF function to count non-empty cells in Google Sheets. Simply enter the target range as the first argument and the “<>” string as the second argument.
Step 5
We can also use the SUMPRODUCT function to achieve a similar result. In the example below, we used the formula “=SUMPRODUCT(LEN(TRIM(A2:A22))>0)” to count non-empty cells.
This formula works by using the TRIM function on each value in the range and comparing the length of the resulting string to 0. The TRIM function removes any hidden characters such as spaces. Cells are considered empty if the length of the string after the TRIM function is less than 0.
Step 6
Next, we will show you how to count non-empty cells without even using a Google Sheets function. First, select the data you want to count.
Step 7
Then, click on the summary item in the taskbar that appears at the bottom right of Google Sheets.
Step 8
You should now see different values that summarize the currently selected range. The “Count:” value is the number of all non-empty cells in the target range.
Conclusion
This guide should have everything you need to count non-empty cells in Google Sheets.
You can make a copy of this example spreadsheet to try it out for yourself.
For more tutorials and tips on Google Sheets, check out Crawlan.com.
