Count Cells With Text in Excel Using the COUNTIF Formula

Formulas & functions · Updated November 20, 2026

If your text is in A2:A100, enter =COUNTIF(A2:A100,"*") to count cells containing text. This works in Microsoft Excel for Microsoft 365 on Windows and is useful for checking lists, labels, notes, or other entries without counting numeric cells.

You can also adapt the COUNTIF criteria to count cells containing a particular word or phrase. Before using the result, decide whether you want every text entry, a particular piece of text, or only non-empty text values; those are slightly different counts.

What You’ll Need

  • An Excel workbook with the data you want to count.
  • The range of cells to check, such as A2:A100. Using a specific range makes it easier to verify your result and avoids scanning unused parts of the sheet.
  • Optional: the word or phrase you want to find, if you only need to count matching text entries.

Step-by-Step

  1. Identify the cells to check. Look at the column or range where the entries are stored. For example, a list of product names might occupy A2:A100, with a heading in A1. Leave the heading out of the range so it is not included in the count. If you are checking another column, replace the example range in the formula with that column’s actual data range.
  2. Choose where to display the answer. Click an empty cell outside the data, such as C2. Putting the result in a separate cell helps prevent the formula from overwriting a list entry. If you intend to copy the count to a report or dashboard, choose a cell that will remain easy to find.
  3. Enter the formula for all text entries. With the data in A2:A100, type =COUNTIF(A2:A100,"*") in the result cell and press Enter. Excel counts cells in that range that contain text. The asterisk is a wildcard meaning any sequence of characters, so it matches text of different lengths. A blank cell and a cell containing a true numeric value are not counted as text by this criterion.
  4. Check the result against a few cells. Scan the range and compare the number with the text entries you can identify. If the result is unexpectedly high, check for text in cells you considered empty, such as a space character, a note, or a formula result. If it is low, check whether some entries are actually numbers or dates rather than text.
  5. Count cells containing a particular word or phrase, if needed. With the entries in A2:A100 and the target phrase “east” anywhere in a cell, enter =COUNTIF(A2:A100,"*east*"). The asterisks allow any characters before and after the word, so “East Office” and “Northeast” both match. COUNTIF is not case-sensitive, so it will also match “EAST” or “East.”
  6. Count a complete text entry instead of a substring. With the same data range and the exact entry “Pending,” use =COUNTIF(A2:A100,"Pending"). This counts cells whose content matches that entry, rather than cells that merely contain those letters as part of a longer entry. COUNTIF still ignores differences in capitalization.
  7. Update the range if your list grows. If you add rows below row 100, expand the formula’s range so the new entries are included. You can also use a larger planned range, such as A2:A500, if that suits your sheet. Avoid including a result cell within the range being counted, or you can create a circular reference or count the formula cell itself.

For a simple example, suppose A2:A7 contains “Blue,” a blank cell, the number 12, “Green,” “In stock,” and the date 10/15/2026 stored as a real Excel date. With that layout, =COUNTIF(A2:A7,"*") returns 3: the three text entries are counted, while the blank, number, and date value are not. If the number 12 were stored as text instead, it would be counted as a text entry.

Common Mistakes

  • Using COUNTA when you mean text only. COUNTA counts cells that are not empty, including numbers, dates, and error values. Use the COUNTIF wildcard formula when the goal is to count text entries rather than all populated cells.
  • Leaving out the wildcards for a partial match. The criterion "east" looks for an exact text entry, while "*east*" finds the letters anywhere in the cell. Add the asterisks on both sides when the target may have other text before or after it.
  • Counting a heading or the formula cell. A range that starts at the column heading will count it if it contains text. Likewise, including the result cell in its own range can make the count wrong or create a circular reference. Check both ends of the range.
  • Assuming a cell that looks blank is truly empty. A cell may contain a space, a nonprinting character, or a formula that returns an empty string. Those contents can make text counts surprising. Inspect the cell or use the non-empty-text alternative in Troubleshooting if empty-string results must be excluded.
  • Expecting case-sensitive matching. COUNTIF does not distinguish uppercase from lowercase. A criterion for “east” can match “EAST.” If capitalization matters, use a different approach rather than changing the COUNTIF capitalization.

Tips

  • Use a bounded range for a responsive workbook. A range such as A2:A1000 is often easier to audit than an entire-column formula, especially if you have many calculations. Extend the range when you add more records.
  • Use Excel’s fill handle when repeating the count. If you copy a formula down, check how its references shift. For a count that should always examine the same range, lock the range with dollar signs. With the source data in A2:A100, enter =COUNTIF($A$2:$A$100,"*") in the result cell before copying; the absolute references stay fixed.
  • Remember that wildcard matching is broad. In COUNTIF criteria, an asterisk stands for any number of characters, and a question mark stands for one character. If the text you are searching for includes a literal wildcard character, put a tilde (~) before it in the criterion. This prevents Excel from treating that character as a pattern.
  • Keep source data consistent. Extra spaces at the start or end of entries can make exact-match counts confusing, even though a contains-text count may still include them. If a list comes from another system, review a few entries for inconsistent spacing and hidden characters before relying on a summary.
  • Separate the question from the formula. Decide whether you are counting any text, a full exact entry, or a substring. Choosing the correct criterion first prevents a correct formula from answering the wrong question.

Troubleshooting

The answer is zero, but text is visible. Confirm that the formula points to the cells containing the entries, not a nearby empty column. Check that the range includes the rows you need and that the formula starts with an equals sign. If you are looking for a word, verify its spelling and that you used asterisks around it for a partial match.

The answer is larger than expected. Check for spaces or other text in cells that look empty, and make sure the range does not include a title, notes, or the result cell. For a targeted count, use a specific criterion rather than the wildcard that matches all text. If the cells contain formulas, inspect what those formulas return; their displayed appearance may not tell you whether the underlying value is text.

You want to exclude formulas that return an empty string. COUNTIF wildcard behavior can be surprising with zero-length formula results. With the values in A2:A100, use =SUMPRODUCT(--ISTEXT(A2:A100),--(A2:A100<>"")) to count text values that are not empty strings. This also excludes cells whose values are numbers or dates. Keep the range bounded, because array calculations over a very large range can slow a workbook.

Some numeric-looking entries are included and others are not. Excel stores numbers entered as numeric values differently from digits stored as text. The wildcard count includes the text version, but not a numeric value. To check a cell, select it and look at the formula bar and number format; imported data may mix both types. Decide whether those text-formatted numbers should count as text for your task.

The result does not change after editing the list. Confirm that the edited cell is inside the formula’s range and that calculation is enabled. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options and choose Automatic if the workbook is set to manual calculation. Press F9 to recalculate. On a Mac, use Formulas > Calculation Options as well; the function and formula syntax are the same.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
Not when they are stored as numeric values. It counts text entries, including digits that have been stored as text. Dates stored as Excel serial numbers are numeric values and are not counted by this text criterion.

How do I count cells that contain a word anywhere?
Put an asterisk before and after the target text in the criterion. For example, with the target in A2:A100, =COUNTIF(A2:A100,"*blue*") counts cells containing “blue” as part of a longer entry. The match is not case-sensitive.

Can COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text comparisons are case-insensitive. If uppercase and lowercase must be treated differently, use a case-sensitive formula or another method designed for that requirement.

Can I count text across more than one column?
Yes. You can give COUNTIF a rectangular range, such as A2:C100, and use the same wildcard criterion to count text cells in that block. Check that the selected rectangle contains only the data you intend to include.

Does this work in Excel for Mac or Excel for the web?
Yes. COUNTIF and the wildcard criteria shown here work in current Excel for Mac and Excel for the web. The formula syntax is the same; ribbon labels or calculation controls can differ slightly between platforms.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.