Exceljet

Quick, clean, and to the point

Count cells that contain text

Excel formula: Count cells that contain text
Generic formula 
=COUNTIF(range,"*")
Summary 

To count the number of cells in range that contain text (i.e. not numbers, not errors, not blank), you can use the COUNTIF function and a wildcard (*). In the example shown, the formula in cell H5 is:

=COUNTIF(data,"*")

where data is the named range B5:B15.

Note: This formula counts cells that contain any text. To count cells that contain specific text, see this formula instead.

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.