Count cells that contain text

Count cells that contain text

Generic formula 

=COUNTIF(rng,"*")

Related formulas 

Count cells that contain specific text

Explanation

To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use 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,"*")

How this 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:

COUNTIFS solution

If you need to restrict counts to meet more specific criteria, you can switch to the COUNTIFs function. For example, to count cells with text, but exclude cells with only a space character, you could use:

=COUNTIFS(range,"*",range,"<> ")

SUMPRODUCT solution

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 returns the sum of ones and zeros.

Count cells that contain text

Generic formula 

=COUNTIF(rng,"*")

Related formulas 

Count cells that contain specific text

Explanation

To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use 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,"*")

How this 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:

COUNTIFS solution

If you need to restrict counts to meet more specific criteria, you can switch to the COUNTIFs function. For example, to count cells with text, but exclude cells with only a space character, you could use:

=COUNTIFS(range,"*",range,"<> ")

SUMPRODUCT solution

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 returns the sum of ones and zeros.