COUNTIF for Text: Exact Matches, Wildcards, and Tips

To count cells that exactly match text in Excel, use =COUNTIF(A2:A7,"East"). It counts matching entries in A2:A7 without distinguishing uppercase from lowercase; add wildcards such as * or ? to count text that only partly matches.

What COUNTIF Does

COUNTIF counts cells in one range that meet one condition. For text, that condition can be a specific word, a pattern, or a comparison built from text and an operator. It is useful for questions such as how many orders have a particular status, how many names begin with a letter, or how often a label appears in a list.

The function has two inputs: the range to inspect and the criterion to apply. The range might be a column of statuses, while the criterion might be the word “Open”. COUNTIF returns a number, not a filtered list of the matching cells. It does not change or mark the source data.

In Microsoft Excel for Microsoft 365 on Windows, enter a formula in a blank cell and press Enter. If your list grows, consider using an Excel table column as the range so the reference can expand when new rows are added. The same COUNTIF function is available in Excel for Mac and Excel for the web; ribbon locations and some keyboard shortcuts differ, but the formula syntax is the same.

Count an Exact Text Match

Suppose A2:A7 contains East, West, East, North, West, and East. To count cells whose content matches East, use this formula. The result is 3.

With East, West, East, North, West, and East in A2:A7, the formula is =COUNTIF(A2:A7,"East"). It returns 3.

Text criteria are enclosed in straight double quotation marks when typed directly into a formula. The quotation marks belong to the formula, not to the cell contents. A cell containing East is counted whether it was typed in uppercase or lowercase, because COUNTIF text matching is not case-sensitive. A cell containing Eastern is not an exact match for East.

For a reusable count, put the criterion in a cell instead of typing it into the formula. For example, if D2 contains East and the values to inspect are in A2:A7, use =COUNTIF(A2:A7,D2). It returns 3 for the example list. Changing D2 to West changes the result to 2 without editing the formula.

Use Wildcards for Partial Text

Wildcards let a criterion match a pattern rather than one complete entry. Excel supports two wildcard characters in COUNTIF criteria:

* matches any number of characters, including no characters.
? matches exactly one character.

For example, imagine A2:A7 contains Red mug, Mug – red, Redwood, Blue mug, red, and Green. The criterion *red* matches any cell with the letters red anywhere in its text. COUNTIF ignores case, so it matches Red mug, Mug – red, Redwood, and red: four cells.

With Red mug, Mug – red, Redwood, Blue mug, red, and Green in A2:A7, use =COUNTIF(A2:A7,"*red*"). The result is 4.

Wildcards can target the beginning or end of a value. In that same list, Red* counts entries that start with Red, including Red mug, Redwood, and red, for a result of 3. The criterion *mug counts entries ending in mug, such as Red mug and Blue mug, for a result of 2.

With the same six entries in A2:A7, the formula =COUNTIF(A2:A7,"Red*") returns 3. With those entries still in A2:A7, =COUNTIF(A2:A7,"*mug") returns 2. These patterns are helpful for inconsistent labels, but a broad pattern can also count unintended entries: *red* would match a longer word such as Redwood. Choose the pattern based on what the data actually means.

The question mark is useful when one character may vary. If A2:A6 contains AB-1, AB-2, AB-10, AC-1, and AAB-1, the pattern AB-? matches AB-1 and AB-2, but not AB-10 because that has two characters after the hyphen. With those entries in A2:A6, =COUNTIF(A2:A6,"AB-?") returns 2.

Count Literal Asterisks and Question Marks

Sometimes a cell contains an actual asterisk or question mark, such as a product code or a note. In a COUNTIF criterion, a tilde tells Excel to treat the next wildcard character literally: ~* means an asterisk, and ~? means a question mark. To match a literal tilde itself, use two tildes in the criterion.

Suppose A2:A5 contains Plan*, Plan A, Plan?, and Plan*. To count cells whose text is exactly Plan* rather than treating the asterisk as a wildcard, use =COUNTIF(A2:A5,"Plan~*"). The result is 2. The tilde is part of the criterion syntax; it is not expected to appear in the cell.

Build Criteria from a Cell

A criterion can refer to a cell, which makes a count easy to update. For an exact match, place the desired text in D2 and point COUNTIF at that cell. Do not add quotation marks around the cell reference inside the formula. Excel reads the value currently stored in D2 as the criterion.

Wildcards can be combined with a cell reference using the ampersand operator, which joins text pieces. If D2 contains the word mug and A2:A7 contains Red mug, Mug – red, Redwood, Blue mug, red, and Green, then the pattern can be assembled around the cell value. With those values in A2:A7 and mug in D2, =COUNTIF(A2:A7,"*"&D2&"*") returns 2: it matches Red mug and Blue mug.

The ampersand joins the wildcard characters to the value in D2. This approach is convenient for a search box or a small summary area: users can enter a new word in D2 and get a new partial-match count. If D2 is empty, the pattern becomes two asterisks, which can match text of any length, so a blank search cell may produce a surprisingly large count. You can prevent that with a separate check or instruction to enter a search term.

Understand Case Sensitivity

COUNTIF treats uppercase and lowercase text as equivalent. A criterion of East counts East, EAST, and east. This behavior is useful for ordinary category labels, where capitalization usually should not change the count, but it is unsuitable when case carries meaning, such as distinguishing case-sensitive identifiers.

For a case-sensitive comparison, EXACT can compare each cell with the desired text, and SUMPRODUCT can add the TRUE results as 1s. If A2:A5 contains East, east, EAST, and West, and D2 contains East, use =SUMPRODUCT(--EXACT(A2:A5,D2)). It returns 1 because only the first entry has the same capitalization as D2. This is a different method from COUNTIF and is most useful when case must genuinely distinguish values.

Check Spaces and Inconsistent Data

A count that seems too low may be caused by spaces or hidden characters. A cell containing East followed by a space is not the same text as East for an exact match. Leading spaces can cause the same problem. Data copied from websites or exported systems may also contain nonbreaking spaces or other characters that are not obvious on screen.

Inspect a few source cells directly, or use Excel’s formula bar to see whether the content differs from what the cell display suggests. For ordinary leading and trailing spaces, TRIM can clean a value in a helper column. If A2 contains a value with extra ordinary spaces, enter =TRIM(A2) in B2 and fill it down. For example, if A2 contains a leading space followed by East, the result in B2 is East. TRIM also reduces repeated internal spaces to one; it does not remove every kind of nonprinting character.

After cleaning a helper column, count the cleaned values rather than the originals. Keep the original data if it may be needed for audit or comparison, and check a sample before replacing anything. If TRIM does not resolve the mismatch, the source may contain nonbreaking spaces or other characters that require a more specific cleanup step. Avoid assuming that two cells are identical just because they look alike.

Common COUNTIF Problems

When a text count looks wrong, check the criterion and the range before changing the data. Common causes include:

Missing quotation marks: Typed text criteria need quotation marks, as in "East". A cell reference such as D2 does not.
An accidental wildcard: An asterisk in the criterion matches many possible strings unless escaped with a tilde.
A range that stops too soon: Confirm that the range includes every row you intend to count.
Spaces or invisible characters: A visually similar label may contain extra characters.
Case assumptions: COUNTIF cannot separate East from EAST. Use a case-sensitive approach if the distinction matters.

Also remember that COUNTIF counts cells that satisfy its criterion; it does not count how many times a word appears inside a sentence. A cell containing “East and West” is one matching cell for a broad criterion that finds East, not two occurrences. If the goal is to count repeated words within each cell, that is a different text-analysis problem and needs a different formula or method.

For more than one condition, use COUNTIFS, which applies multiple criteria to corresponding ranges. For instance, a table might need to count rows where the region is East and the status is Open. COUNTIFS is appropriate because both conditions must be satisfied on the same row; two separate COUNTIF results would not by themselves establish that relationship.

Frequently Asked Questions

How do I count cells containing a word anywhere in the text? Use a wildcard on both sides of the word, such as =COUNTIF(A2:A20,"*East*"). Replace the range and word with your own. This matches East within longer text too, so it may also count a word such as Eastern.

Does COUNTIF distinguish uppercase and lowercase? No. COUNTIF text matching is case-insensitive, so East and EAST count as matches for the same text criterion. Use EXACT with SUMPRODUCT when capitalization must be significant.

How do I count cells that start with specific text? Put an asterisk after the starting text, such as =COUNTIF(A2:A20,"East*"). The asterisk allows any characters after East, including none.

How do I count a literal asterisk in a cell? Escape it with a tilde in the criterion. For example, =COUNTIF(A2:A20,"Plan~*") looks for the literal text Plan* rather than treating the asterisk as a wildcard.

Why does COUNTIF miss a value that looks identical? Check for leading or trailing spaces, nonprinting characters, or a range that does not include the cell. COUNTIF ignores letter case, so capitalization alone is not the cause. A helper column using TRIM can remove ordinary extra spaces.

Can COUNTIF count text based on more than one condition? COUNTIF applies one criterion to one range. For multiple conditions on corresponding rows, use COUNTIFS, such as when the region and status must both match on the same record.

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.