Last Updated on January 10, 2023 by Jake Sheridan
In this tutorial, you will learn how to count non-empty cells in Google Sheets
How To Count Non-Empty Cells in Google Sheets
When working on 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 may want to know how many of these rows actually have values.
In Google Sheets, there are a few ways you can 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 since it counts the number of cells that meet certain criteria. We may also use the SUMPRODUCT function to count the 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 Number of Non-Blank Cells in Google Sheets
Here’s how to count the number of non-blank cells in Google Sheets.
First, select a new cell to place the number of non-empty cells in our target range.
In this example, we’ll use the cell D1 to output the number of non-empty cells in the range A2:A22.
Next, head to the Google Sheets formula bar and type ‘=COUNTA(‘ to start the COUNTA function.
The COUNTA function will return the number of values provided in the argument. We’ll use COUNTA rather than COUNT since the latter only considers numerical data.
Add the target range to count as the argument of the COUNTA formula. Hit the Enter key to return the final count.
In this example, we’ve determined that the range has 17 non-empty cells.
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.
We may also use the SUMPRODUCT function to accomplish 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 blank if the length of the string after the TRIM function is less than 0.
Next, we’ll show you how to count non-empty cells without even using a Google Sheets function. First, select the data you want to count.
Next, click on the summary element on the task bar seen on the bottom-right of Google Sheets.
You should now see different values that summarize the currently-selected range. The ‘Count:’ value is a count of all non-empty cells in the target range.
This guide should be everything you need to count the non-empty cells in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.