You manage a list of employee feedback comments in Google Sheets. Each row in column A has either a comment or is left blank. You need to know how many employees have provided feedback.
To find this out, you can count the cells with text in column A.
- You can use COUNTIF with a wildcard asterisk to count cells with a text string =COUNTIF(B2:B10, “*”).
- You can also use SUMPRODUCT with ISTEXT to count the number of cells with the text values.
- To count cells with a specific text, you can use COUNTIF with criteria as the text.
- You can use a wildcard asterisk with the COUNTIF to count cells with partial text.
In the tutorial, we will learn many formulas that can help you count cells with text in Google Sheets. So, let’s get started…
Count Cells with Text in Google Sheets
The easiest and best way to count cells with text is to use COUNTIF with an asterisk. The COUNTIF function counts the number of cells in a range that meets a specific condition.
When using an asterisk in COUNTIF as a criterion, it counts the number of cells with any text in the range from B2 to B10.
The asterisk (*) is a wildcard character representing any text, so it helps count all non-empty text cells within the specified range.
By using the same sample data that we have seen above, enter the formula in any empty cell to get the count of non-empty cells with text:
=COUNTIF(B2:B11, "*")
When you enter this formula, it counts the number of cells in the range B1 to B10 that contain any text. In our example, it will return 5, as the list has four feedback comments. It’s a quick way to see how many employees have shared their thoughts.
In the same way, you can also use SUMPRODUCT with ISTEXT to count the total number of cells in a range with text values.
=SUMPRODUCT(ISTEXT(B2:B11)*1)
The formula =SUMPRODUCT(ISTEXT(B2:B10)*1) sounds complex but straightforward. The ISTEXT checks each cell in the range B2 to B10 to see if there’s text inside.
It gives a TRUE for text and FALSE for empty cells. When you multiply these TRUE/FALSE values by 1, TRUE becomes 1, and FALSE becomes 0.
In the end, SUMPRODUCT adds up all the 1s, giving you the total count of cells with text. So, you quickly know how many employees have given their feedback!
Problem with a Whitespace in a Cell
When counting cells with text in Google Sheets, whitespace can cause unexpected issues. Whitespace includes spaces, tabs, or any invisible characters at the beginning, end, or even within the text in a cell.
In the above example, you can see we have a whitespace in cell B3, count it as a text value, and then change the result.
You can use the TRIM function to solve this, which removes all leading and trailing spaces from text in cells.
By adding TRIM into your formulas, you can ensure that only actual text cells are counted. To deal with this, you need to use SUMPRODUCT.
=SUMPRODUCT((LEN(TRIM(B2:B11))>0) * ISTEXT(TRIM(B2:B11)))
To understand this function, you need to split this formula into parts.
- The TRIM function removes extra spaces from each cell from B2 to B11.
- Then, LEN checks the length of the trimmed text, and LEN(TRIM(B2:B11))>0 ensures the cell isn’t empty or just whitespace.
- ISTEXT function checks if the trimmed content is indeed text.
- SUMPRODUCT multiplies these checks, converting TRUE to 1 and FALSE to 0, and adds them up.
Using ARRAYFORMULA with LEN to Count Cells with Text
You can use a combination of ARRAYFORMULA and LEN to count cells with text more dynamically and flexibly, which is especially useful when dealing with larger datasets.
=COUNTA(ARRAYFORMULA(IF(LEN(B2:B11)>0, B2:B11, )))
Count Cells with a Specific Text (Exact Value)
There are three ways to write formulas to count cells with an exact value in the cell. You can use COUNTIF for this and specify the text for which you want to count the cells with the text in the criteria.
=COUNTIF(A2:A11, "John")
COUNTIF looks at the range from A2 to A11 and counts the cells that exactly match the text “John”. It’s like telling Google Sheets, “Hey, check these cells and tell me how many times you see John”.
In the same way, you can use the SUMPRODUCT with ISNUMBER and SEARCH functions to count the cells of a specific text.
=SUMPRODUCT(--ISNUMBER(SEARCH("John", A1:A10))) 
- SEARCH looks for the text “John” in each cell from A1 to A10. If the cell contains “John”, it returns a number; if it doesn’t, it returns an error.
- The ISNUMBER then checks if the result is a number, returning TRUE for numbers and FALSE for errors. Using –, we turn these TRUE/FALSE values into 1s and 0s.
- SUMPRODUCT adds up all the 1s, giving you the total count of cells containing “John”.
And if you want to use a different formula, you can combine COUNTA and FILTER. See the example below, where we used the same criteria to count the cells with the value “John”.
=COUNTA(FILTER(A1:A10, A1:A10="John"))
FILTER goes through the range A1 to A10 and keeps only the cells that exactly match “John”. The COUNTA then counts these filtered cells, telling you how often “John” appears in your list.
Count Cells with a Partial Text
Like all the formulas we have discussed above, you can use these formulas to count the cells with a partial text.
=COUNTIF(A2:A11, "*John*")
To understand this formula, you need to understand the asterisk wildcard character: The asterisk (*) wildcard character in Google Sheets represents any sequence of characters, making it helpful in finding partial matches.
In this formula, COUNTIF checks each cell from A2 to A11. The asterisks * are wildcards that mean “any sequence of characters”.
So, *John* will match any cell with “John” anywhere in its text, whether at the beginning, middle, or end of the cell value.
Now, we have a combination of SUMPRODUCT and SEARCH to count the cells with partial text. This is the same method we discussed above for counting the cells with the exact match.
