In this tutorial, you will learn how to count cells in a range with text in Google Sheets.
How To Count Cells with Text in Google Sheets
Quick Navigation
One way to make data analysis easier is to count the number of cells in a range that contain text.
This can be helpful if you want to find out the amount of non-empty cells that exist in a range.
Knowing this number can provide a more accurate measurement of the number of entries in a dataset.
In this guide, we will show you how to use two different formulas to count all cells with text in Google Sheets: COUNTA and SUMPRODUCT.
We’ll explain how each formula works, and provide an example of how to use them in a spreadsheet.
How to Determine How Many Cells Have Text in a Range in Google Sheets
Here’s how to determine how many cells have text in a range in Google Sheets.
Step 1
We’ll first explain how to count cells with text using the COUNTA function.
Select an empty cell to place the new formula.
Step 2
Type the string “=COUNTA(“ into the formula bar to start the COUNTA formula.
Step 3
The COUNTA function requires at least one argument. The user can provide a list of cells to check or provide a particular cell range to scan through.
In our example, we’ve counted 16 non-empty cells in the range A2:A22.
Step 4
We can also find the number of cells with text using the SUMPRODUCT function. Type the formula =SUMPRODUCT(LEN(TRIM(A2:A22))>0) to count all cells with text in the range A2:A22.
We used the TRIM function to prevent counting cells with just whitespace. The LEN function counts the number of characters from the trimmed text. If the result is greater than 0, it should be counted as TRUE or 1.
Step 5
Alternatively, users can also find the number of non-empty cells through the Summary section found in the lower-right-hand side of the sheet.
This method only requires the user to select the cell range to count with.
Summary
This guide should be everything you need to count cells in a range with text in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.