Count cells that contain text

Count cells that contain text

Related Functions

Sponsored Link

=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.

Do you want to count cells that contain specific text? See this formula instead.

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.

Sponsored Link

0 votes. 0 / 5

Excel - Excel Functions - Excel Formulas
Logo