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.

Web : excel

Advertisements