In Google Sheets, multiple ways exist to count the number of cells with text values. And in this tutorial, we will learn to write different formulas for this.
Count Number of Cells with Text
The easiest way is to write a formula by combining SUMPRODUCT and ISTEXT. And the formula will be:
=SUMPRODUCT(--ISTEXT(A1:A5))To write this formula:
- Enter SUMPRODUCT in a cell.
- Type double minus sign.
- Enter ISTEXT and refer to the range where you have values.
- Close the function and hit enter to get the result.
In the above example, we have three cells with text, the first two and the last. Now here’s one thing that you need to understand in the last cell, you have a bracket by Google Sheets; treat it as a text. And that’s why you have 3 in the result.
How this Formula Works
ISTEXT returns an array where TRUE is for text values and FALSE for other values. After that, the “–” double minus sign converts TRUE and FALSE into 1 and 0. In the end, SUMPRODUCT returns the sum of the values from the array, equivalent to the count of cells with text.
Alternate Formula to Count Cells with Text
There’s one more formula that you can use to get the count of cells with text. This formula needs you to use the COUNTIFS and Wildcard Characters.
=COUNTIFS(A1:A5,"*",)Now using this formula has a plus point. Let’s say you have a cell in the range where you have blank space you can’t identify, but Google Sheets will consider it while getting the result.
By little tweaking this formula, you can fix this. You need to use one more criteria in the function to exclude the blank space. And for this, you can use does not equal operator and a blank space.
=COUNTIFS(A1:A5,"*",A1:A5,"<> ")Count Cells with Specific Text
You can also use COUNTIFS to count cells with a specific text.
In the above example, you have COUNTIFS, which counts the cells with the ” Hello ” text.
=COUNTIFS(A1:A5,"Hello")Count Case Sensitive Text
If you want to count the cells with a case-sensitive match, you can use SUMPRODUCT with EXACT.
=SUMPRODUCT(--EXACT("HELLO",A1:A5))In this formula, EXACT tests values for using case-sensitive match and return an array as TRUE and FALSE. After that, the double minus sign (–) converts TRUE and FALSE into 1 and 0. In the end, SUMPRODUCT returns the count of the cells with the exact text with the exact case.