Count cells that contain text

=COUNTIF(range,"*")
To count the number of cells in range that contain text (i.e. not numbers, not errors, not blank), you can use the COUNTIF function and a wildcard (*). In the example shown, the formula in cell H5 is:
=COUNTIF(data,"*")
where data is the named range B5:B15.
Note: This formula counts cells that contain any text. To count cells that contain specific text, see this formula instead.
In this example, the goal is to count cells in a range that contain any kind of text. This could be hardcoded text like "apple" or "red", numbers entered as text, or formulas that return text values. The solution uses the COUNTIF function, which counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as an asterisk (*), which is a the wildcard that matches any number of text characters. The formula in H5 is:
=COUNTIF(data,"*") // all text values
The formula in H6 counts cells in data that do not contain text:
=COUNTIF(data,"<>*") // non-text values
This formula uses the not equal to (<>) logical operator.
Notes:
- The logical values TRUE and FALSE are not counted as text.
- Numbers are not counted by "*" unless they are entered as text.
- Empty cells are not counted as text.
COUNTIFS option
To apply more specific criteria, you can switch to the COUNTIFs function, which supports multiple conditions. For example, to count cells with text, but exclude cells that contain only a space character, you can use a formula like this:
=COUNTIFS(range,"*",range,"<> ")
SUMPRODUCT option
You can also use the SUMPRODUCT function to count text with the ISTEXT function like this:
=SUMPRODUCT(--ISTEXT(range))
The double negative (--) coerces the TRUE and FALSE results from ISTEXT into to 1s and 0s. SUMPRODUCT then sums the result.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.