COUNTIF Excel: Count Cells That Contain Text in a Range

Formulas & functions · Updated November 22, 2026

To count cells that contain text in Excel, use =COUNTIF(A2:A100,"*"), replacing the range with your own. The asterisk is a wildcard: it matches text strings, while truly blank cells and numeric values are not counted.

The quick formula and what it counts

In Microsoft Excel for Microsoft 365 on Windows, COUNTIF takes a range and one criterion. The criterion "*" means “any sequence of text characters,” so it is a quick way to count text entries without listing each value. It is not a count of every occupied cell: numbers and dates stored as numeric values are excluded.

For a concrete example, assume A2:A7 contains North, the number 42, a truly blank cell, South, NORTH, and the number 17, respectively. With that layout, =COUNTIF(A2:A7,"*") returns 3: North, South, and NORTH. COUNTIF is not case-sensitive, so the two spellings of North are both included.

This is useful for checking imported lists, counting labels in a mixed column, or getting a quick sense of how many entries are text rather than numbers. The important question is what you mean by “has text”: any text value, at least one visible character, or a particular word inside a longer entry. Those tasks can call for different criteria.

How COUNTIF and wildcards work

COUNTIF checks each cell in the range against a criterion and adds one for every match. A criterion can be a value, a comparison such as greater than a number, or a text pattern. For text patterns, Excel recognizes two main wildcards:

  • * matches any number of characters, including a sequence of different letters, digits, or spaces.
  • ? matches one character. Several question marks match the same number of characters.

Wildcards are especially useful when you do not know the full cell contents. To count entries containing “or” anywhere, assume the sample data is still in A2:A7. The formula =COUNTIF(A2:A7,"*or*") returns 2, matching North and NORTH. The asterisk on each side permits other characters before and after the letters. Text matching is case-insensitive, so this criterion does not distinguish uppercase from lowercase.

To count text entries that begin with N, with A2:A7 holding the sample values, use =COUNTIF(A2:A7,"N*"). The result is 2. A criterion of "*th" would instead match text ending in “th.” If you need to match a literal asterisk, question mark, or tilde rather than use it as a wildcard, put a tilde before that character. For example, a criterion intended to find a literal asterisk uses "~*".

Count any text versus text with at least one character

The asterisk criterion is a convenient way to count cells containing text, but sometimes you specifically want a string with one or more characters. In that case, use "?*": the question mark requires at least one character, and the asterisk allows any additional characters.

Assuming the same sample values in A2:A7, =COUNTIF(A2:A7,"?*") returns 3. This is also a useful choice when a range contains formulas that can return an empty string. A formula displaying nothing is not the same thing as a cell that has never contained anything; it can behave differently in blank and text counts. Requiring a character makes the intent explicit. A cell containing only a space still has a character and can match, even though it may look empty on screen.

If you need to count all cells with a value, not just text, use a different method. COUNTIF with a text wildcard is not a general “nonblank” test. For example, a column of product IDs may contain both text IDs and numeric IDs, and a text-only count will not include IDs stored as numbers. Choose the criterion based on the cell values you actually need to include.

Count an exact entry or use a criterion cell

Wildcards are not required when you want to count a particular text value. With North in A2:A7, =COUNTIF(A2:A7,"North") returns 2 for the sample because COUNTIF ignores capitalization. It matches the whole cell value, rather than finding North inside a longer phrase such as “North Office.” To find text within a longer entry, add wildcards around the word, as in the earlier example.

You can also put the criterion in a worksheet cell, which is convenient when the search term changes often. Suppose A2:A7 holds the sample values and D2 contains N*. With that layout, =COUNTIF(A2:A7,D2) returns 2. Edit D2 to change the pattern without rewriting the formula. If D2 contains an ordinary word rather than a wildcard pattern, COUNTIF uses it as an exact, case-insensitive match.

Criteria that include comparison operators need special care. For example, a criterion beginning with > or < is generally interpreted as a comparison, not as a request to find text beginning with that symbol. When you are counting literal symbols or values that look like operators, check that Excel is treating the criterion as text and use a wildcard or escape character where appropriate.

Text, numbers stored as text, and dates

Excel distinguishes a value’s underlying type from how it looks. A number formatted to display with letters or leading zeros is still a number if Excel stores it as numeric data. The wildcard text count will not include it. Conversely, a number imported as text—for example, a code with a leading apostrophe—can match "*" because its stored value is text.

Dates are commonly stored as serial numbers and displayed with a date format. A date that is a genuine Excel date is numeric, so it is not counted by the text wildcard. A date imported as a text string can be counted. This difference often explains why a text count seems inconsistent with what is visible in a column. Changing the number format changes the display; it does not necessarily convert the underlying value.

To investigate, select a suspicious cell and look at the formula bar and number format, or try sorting and filtering the data by type. If the values came from a CSV or another system, check whether conversion or import settings preserved them as text. Avoid converting an entire column blindly: identifiers such as postal codes can need to remain text so that leading zeros are preserved.

Common problems and practical checks

If the result is zero or smaller than expected, first check the range and the actual contents. Extra spaces, nonprinting characters, and inconsistent data types are frequent causes. A cell that looks like “North” may contain a trailing space, and a number that looks like an identifier may be stored as a number rather than text.

  • Check the selected range. Confirm that it includes the intended rows and does not accidentally include a heading or omit new data below the list.
  • Check for spaces. Leading or trailing spaces are text characters. They can affect exact-match criteria, although a wildcard count may still include the cell.
  • Check whether numbers are text. Values imported from another application may have mixed types even when they look alike.
  • Check formula-generated blanks. A formula displaying an empty string can differ from a truly empty cell. Use a criterion that requires a character if empty text should not count.
  • Check the criterion’s quotation marks. In a formula, text criteria need straight double quotes; curly quotation marks copied from formatted text can cause errors.

COUNTIF does not clean the data; it only tests what is in the cells. If inconsistent spaces are the issue, clean a copy of the data before counting, or use a helper column to standardize values. That keeps the count formula straightforward and makes the cleanup visible rather than hiding it inside a complicated criterion.

COUNTIF compared with other counting options

Use COUNTIF when one condition is enough, such as “count text entries” or “count entries containing this word.” It is also suitable for one exact value or one wildcard pattern. It is not case-sensitive, and it accepts a single range-and-criterion pair.

Use COUNTIFS when the count must meet multiple conditions, such as a text category in one column and a date period in another. Use COUNTA when the goal is to count cells that are not empty in the ordinary worksheet sense, rather than to count text only; note that formula results displaying empty strings can affect what counts as occupied. Use COUNTBLANK when the goal is to count blank cells, including some cells with formulas returning empty text. These functions answer different questions, so swapping one for another can change the result.

For the sample data in A2:A7, =COUNTBLANK(A2:A7) returns 1 because there is one truly blank cell. That result is not the same as the text count of 3. Keeping the question clear—text, nonempty cells, or blank cells—helps you choose the right function and explain the result to someone else.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
Not when Excel stores them as numeric values. A number stored as text can match the wildcard, even if it looks like an ordinary number in the worksheet.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text criteria are case-insensitive, so “North” and “NORTH” match the same ordinary text criterion. Use a case-sensitive method if capitalization is part of the requirement.

How do I count cells containing a specific word anywhere?
Place an asterisk before and after the word in the criterion. For example, the pattern "*or*" matches a cell with “or” anywhere in its text, regardless of capitalization.

How do I count text cells but exclude formulas that display blank?
Use the "?*" criterion to require at least one character. Remember that a cell containing a space is not empty to this test, even if it appears blank.

Why does my text count miss a date or code?
The value may be stored as a number and only formatted to look like text, or it may be stored as text. Excel counts based on the stored value, not just its display. Check the source data and cell format before converting anything.

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.