=COUNTIF(rng,"*")
If you need to count the number of cells that contain text (i.e. not numbers, not errors, not blank), you can do so with the COUNTIF function and a wildcard. In the generic form of the formula (above), rng is a range of cells, and “*” is a wildcard matching any number of characters.
In the example, the active cell contains this formula:
=COUNTIF(B4:B8,"*")Here’s how the formula works:
COUNTIF counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as the wildcard character “*” which matches any number of text characters.
A few notes:
- The logical values TRUE and FALSE are not counted as text
- Numbers are not counted by “*” unless they are entered as text
- A blank cell that begins with an apostrophe (‘) will be counted.
You can also use SUMPRODUCT to count text values along with the function ISTEXT like so:
=SUMPRODUCT(--ISTEXT(rng))The double hyphen (called a double unary) coerces the result of ISTEXT from a logical value of TRUE or FALSE, to 1’s and 0’s. SUMPRODUCT then sums these values together to get a result.