Count cells that contain text

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.