To count cells containing at least one text character while ignoring numbers and true blanks, use =COUNTIF(B2:B8,"?*").
If a formula-produced empty string should count as text, use =SUM(ARRAYFORMULA(--ISTEXT(B2:B8))) instead.
Count Cells with Text Using COUNTIF
Our sample range contains ordinary text, a number, a true blank, a formula returning "", one space, a symbol, and mixed letters and numbers.
| Cell | Value or formula | Meaning |
|---|---|---|
| B2 | Project Alpha | Ordinary text |
| B3 | 42 | A number |
| B4 | (leave empty) | A truly empty cell |
| B5 | ="" |
A zero-length text result |
| B6 | =" " |
One space |
| B7 | # | A text symbol |
| B8 | A12 | Letters and numbers stored as text |
Replace B2:B8 in the formulas with your own range. Exclude a text header unless you want it included in the count. Put the result outside the counted range.
Enter this formula in an empty cell:
=COUNTIF(B2:B8,"?*")
The question mark requires one text character. The asterisk allows any additional characters. Numbers and true blank cells do not match this text pattern.
A space and a symbol are characters, even when they are not words. Clean unwanted spaces before counting if they should not qualify as text.
In the tested sample, the formula returns 4. It counts Project Alpha, the one-space value, #, and A12.
Count Text Including Empty Strings with COUNTIF or ISTEXT
You will often see this shorter formula:
=COUNTIF(B2:B8,"*")
Google’s COUNTIF documentation defines * as zero or more contiguous characters. By contrast, the ?* pattern requires at least one character.
The tested formula returns 5. It includes the same four cells plus B5, whose formula returns a zero-length string.
If you specifically want Sheets to test the value type, use:
=SUM(ARRAYFORMULA(--ISTEXT(B2:B8)))
ISTEXT returns TRUE for text, including an empty string, and FALSE for a truly blank cell. The double unary changes TRUE and FALSE to 1 and 0 before SUM adds them.
Alternative: SUMPRODUCT with ISTEXT
You can also count text values with:
=SUMPRODUCT(--ISTEXT(B2:B8))
This returned 5 in the same live test. It counts text by value type, including the empty-string formula, without requiring an explicit ARRAYFORMULA wrapper.
Use either this formula or the preceding ISTEXT formula; they solve the same counting task. See Google’s SUMPRODUCT reference for how it combines array values.
Why COUNTA Counts More Than Text
The COUNTA function counts every value, not only text.
=COUNTA(B2:B8)
Google states that COUNTA includes numbers, zero-length strings, and whitespace. It therefore answers “How many cells contain values?” rather than “How many cells contain text?”
COUNTA returns 6 in the sample because it also counts numeric 42. The true blank is the only cell it skips.
Count Cells with Specific Text (Exact Match)
Use an exact text criterion when the whole cell must match:
=COUNTIF(B2:B8,"Project Alpha")
This returns 1 for Project Alpha in B2. A cell containing “Project Alpha review” would not be an exact match.
COUNTIF compares each cell with the criterion and counts matching cells. Text criteria require quotation marks, and COUNTIF matching is not case-sensitive.
You can replace the typed criterion with a cell reference. For example, =COUNTIF(B2:B8,G2) counts cells equal to the value entered in G2.
Count Cells Containing Partial Text
Place an asterisk on both sides of the search text when the cell may contain other characters:
=COUNTIF(B2:B8,"*Alpha*")
The result is 1 in this sample.
This pattern matches Alpha anywhere in a cell. COUNTIF counts each matching cell once, even when the searched text occurs more than once inside that cell.
For a criterion stored in G2, join the wildcards to the reference:
=COUNTIF(B2:B8,"*"&G2&"*")
For more criteria patterns, see the COUNTIF function guide.
Other Google Sheets articles you may also like