AI Excel Generator
← ALL GUIDES

How to Count Cells Containing Text in Excel

Updated November 4, 2026 · AI Excel Generator

To count cells containing text in Excel, use COUNTIF with a wildcard or combine ISTEXT with SUMPRODUCT for more control.

The right formula depends on whether you mean any text, a particular word, or a text fragment inside a cell.

Count cells containing any text

For a range such as A2:A20, this formula counts cells that contain text:

=COUNTIF(A2:A20,"*")

The asterisk wildcard matches text strings of any length. This criterion does not count truly empty cells or numeric values stored as numbers. If imported numeric-looking values are stored as text, use ISTEXT when you need a direct data-type test.

For example, if A2:A6 contains Apple, 12, a blank cell, 42 apples, and TRUE, the formula returns 2: the two entries containing text are Apple and 42 apples.

Excel treats logical values such as TRUE as non-text for this count.

Count text values with ISTEXT

When you need to test each cell's data type directly, use ISTEXT inside SUMPRODUCT:

=SUMPRODUCT(--ISTEXT(A2:A20))

ISTEXT returns TRUE for each text value and FALSE for other values.

The double unary operator (--) converts those logical results to 1s and 0s, and SUMPRODUCT adds them.

This method explicitly counts text values, including text returned by a formula, but does not count numbers, logical values, or genuinely empty cells.

Use this approach if you want the formula's intent to be clear to someone reviewing the workbook.

It works in current desktop versions of Excel, including Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.

Count cells containing a specific word or phrase

To count cells containing a particular word anywhere in the cell, add asterisks around the search text:

=COUNTIF(A2:A20,"*apple*")

This counts cells such as Green apple, apple pie, and pineapple.

COUNTIF is not case-sensitive, so it also matches APPLE and Apple.

To put the search term in another cell, suppose D2 contains apple.

Join the wildcard characters to that cell reference:

=COUNTIF(A2:A20,"*"&D2&"*")

Using a cell reference makes the search reusable: change the value in D2 to count a different word without editing the formula.

If the search text includes an asterisk, question mark, or tilde that should be matched literally, prefix that character with a tilde in the criteria; these characters otherwise have special wildcard behavior.

Count cells that begin or end with text

Use a wildcard on just one side when the text must occur at the start or end of the cell:

  • =COUNTIF(A2:A20,"apple*") counts entries beginning with apple.
  • =COUNTIF(A2:A20,"*apple") counts entries ending with apple.

These formulas also match longer words that share the specified beginning or ending.

For example, apple* can match both apple pie and applesauce.

Count cells that contain text but not numbers

The wildcard formula counts cells whose stored value is text.

A number formatted to look like text is still a number if Excel stores it numerically, while a value imported with a leading apostrophe or formatted as text may be stored as text.

To include formula results that return an empty string, such as =IF(B2="","",B2), use ISTEXT because that result is text even though it looks blank:

=SUMPRODUCT(--ISTEXT(A2:A20))

By contrast, COUNTIF(A2:A20,"*") generally excludes formula results that are empty strings.

Choose the method based on whether those visually blank formula results should count as text in your workbook.

Count nonblank cells instead of text cells

If your goal is to count every filled cell, including numbers and dates, use COUNTA:

=COUNTA(A2:A20)

COUNTA counts cells containing any value, including text, numbers, errors, and formulas that return an empty string.

It does not tell you which cells contain text specifically, so it is not a substitute for COUNTIF or ISTEXT when data type matters.

Common issues and formula checks

  • The result is zero: Check that the formula refers to the correct range and that the cells actually store text.

    Numeric values do not match the text wildcard.
  • Unexpected cells are included: A cell containing spaces is text, even if it appears empty.

    Remove unwanted spaces or use a helper column to clean the data.
  • Partial matches are counted: The criteria "*apple*" also matches longer strings such as pineapple.

    Use a stricter data format or a more specific formula if whole-word matching is required.
  • Case matters: COUNTIF ignores letter case.

    For case-sensitive matching, a formula using EXACT and SUMPRODUCT can compare values precisely.
  • The formula shows an error: In some regional settings, Excel uses semicolons rather than commas between arguments.

    For example, write =COUNTIF(A2:A20;"*") if your Excel expects semicolons.