COUNTIF Not Working for Text? Fix the Common Causes

Yes—Excel COUNTIF can count text, and it normally ignores letter case. For an exact match, use =COUNTIF(A2:A10,"Apple"); if the result is wrong, check the criteria, spaces, wildcard characters, and whether the cells contain the text you think they do.

How COUNTIF matches text

COUNTIF takes two arguments: the range to inspect and the criterion to apply. The criterion can be text typed in quotation marks, a cell reference, or a condition built from an operator and a value. Text criteria in Excel are not case-sensitive, so Apple, APPLE, and apple are treated as the same match.

With the words Apple, apple, pear, apple pie, and a blank cell in A2:A6, the exact-match formula =COUNTIF(A2:A6,"Apple") returns 2: it counts Apple and apple, but not apple pie. COUNTIF treats a plain text criterion as a whole-cell match unless the criterion contains a wildcard.

This distinction explains many apparent failures. A cell that displays Apple pie does not exactly equal Apple, even though it contains the word Apple. Conversely, a criterion containing an asterisk or question mark may match more cells than a reader expects because those characters have special meaning in COUNTIF criteria.

Check the criterion and range first

Start by confirming that the formula points at the intended cells and that the criterion is spelled correctly. A range shifted by one row, a reference to the wrong column, or a criterion copied from a different cell can produce zero or an unexpectedly small count. If you use a criterion cell, inspect its actual contents rather than relying only on how it looks on screen.

With fruit names in A2:A6 and the word Apple in D2, =COUNTIF(A2:A6,D2) returns 2 for the example list above. The criterion reference does not need quotation marks; quotation marks are for literal text entered directly in the formula.

If you want to include an operator such as greater than or less than in a criterion, join the operator to the value with an ampersand. This is more common with numbers and dates than with ordinary text. For example, with scores in B2:B20 and a threshold in E2, =COUNTIF(B2:B20,">"&E2) counts values greater than the threshold. This example assumes E2 contains 70 and the range has 75, 70, and 82 among its values; the result is 2.

Check that the first argument is the cells you intend to count.
Check that literal text criteria are surrounded by straight quotation marks.
Check for a misspelling, an extra character, or a criterion cell that is blank.
Check that a comparison operator is joined to a referenced value, rather than entered as disconnected text.

Exact matches, partial matches, and wildcards

Use an exact text criterion when the entire cell should match. To find text that appears anywhere within a cell, put an asterisk on both sides of the search text. An asterisk stands for any number of characters, including none. A question mark stands for exactly one character. These are matching rules, not ordinary punctuation, unless you escape them.

With Apple, apple, pear, apple pie, and a blank in A2:A6, =COUNTIF(A2:A6,"*apple*") returns 3. It counts Apple, apple, and apple pie because each contains the sequence apple; it still ignores case. With product codes in A2:A10, =COUNTIF(A2:A10,"AB?12") would match a five-character value beginning AB, followed by one character, and ending 12.

To search for a literal asterisk, question mark, or tilde, put a tilde before the character. With the values * , A*, and A1 in A2:A4, =COUNTIF(A2:A4,"~*") returns 1 because it searches for a cell whose text is a literal asterisk. The tilde itself is also a special character in wildcard criteria; use two tildes to search for a literal tilde.

Think about whether the desired rule is “is exactly this text,” “starts with this text,” or “contains this text.” For example, a criterion of North* finds text beginning with North; *North* finds text containing North anywhere; and North finds only the exact text North. Choosing the wrong pattern is a common reason a count looks too high or too low.

Spaces and hidden characters

Two entries can look identical but differ because one contains a leading space, a trailing space, or a nonprinting character. A normal space at the beginning or end of a cell makes the stored text different from the clean version. Pasted data from a website, PDF, or another system can also contain a nonbreaking space, which looks like a regular space but is a different character.

For a quick check, click the cell and inspect the formula bar, or edit the cell and look at the start and end of the text. You can also compare the visible length with the expected number of characters, though that method is easiest when the intended text length is known. Avoid assuming that changing the cell’s number format will remove unwanted characters; formatting changes appearance, not the underlying text.

For a small list, clean the source cells directly by editing the text. For a larger list, use a helper column and review the results before replacing or relying on the original data. With the raw entry in A2, =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) replaces nonbreaking spaces with ordinary spaces and removes leading and trailing ordinary spaces; it also reduces repeated ordinary spaces between words to one. If A2 contains a pasted version of “Apple ” with a trailing nonbreaking space, the result is Apple. Fill the formula down for the other rows, then use the cleaned column as COUNTIF’s range.

TRIM does not remove every possible invisible character. If the source contains line breaks or other control characters, identify the character before applying a cleanup formula; broad substitutions can alter legitimate text. Keeping the original data in a separate column makes it easier to inspect and correct a cleanup that changes more than intended.

Text that looks like a number

Codes such as 00125, postal codes, and IDs often look numeric but need to remain text because their leading zeros are meaningful. Other cells may contain the number 125, while neighboring cells contain the text “125.” This difference can matter in comparisons and calculations. COUNTIF criteria can have rules for numeric-looking criteria, so do not diagnose a problem from appearance alone.

Click a cell and look at the formula bar, or use Excel’s cell-format and warning indicators to investigate how an entry was imported. The format displayed in the ribbon does not, by itself, prove what type of value is stored: changing a cell to Text after a number has already been entered does not necessarily convert the existing value into text. If exact ID matching is essential, standardize the source column deliberately and test the count on a small known sample.

For text codes, preserve a consistent representation in both the data and the criterion. For instance, 00125 and 125 should not be treated as interchangeable if the leading zeros identify a distinct code. Import settings, formulas used to create the column, or a controlled cleanup step may be needed to keep the whole column consistent.

When you need a case-sensitive count

COUNTIF is case-insensitive by design. If uppercase and lowercase letters must be distinguished—for example, when codes ABC and Abc are different—use EXACT to compare letter case and SUMPRODUCT to add the TRUE results. With codes in A2:A6 and the case-sensitive search value ABC in D2, =SUMPRODUCT(--EXACT(A2:A6,D2)) returns 1 if the list contains ABC, Abc, abc, and two other codes. EXACT compares corresponding text values and is case-sensitive; the double unary converts its TRUE/FALSE results to 1/0 for SUMPRODUCT to add.

This approach is useful when case is part of the data’s meaning, but it is not a general fix for a zero COUNTIF result. If case should not matter, COUNTIF is simpler and already ignores it. For very large datasets, consider whether a helper column or a more structured data-cleaning step would make the workbook easier to maintain.

Common formula errors and practical checks

When a result is zero, troubleshoot in a consistent order rather than rewriting the formula at random. First test one cell that should match. Then widen the check to the full range, and finally inspect how the source data was created. This helps separate a formula problem from a data-quality problem.

Confirm the range includes the expected row and does not accidentally include a header instead of the data.
Copy a known cell’s text into a temporary criterion cell, then compare the count with the typed criterion.
Remove or standardize leading and trailing spaces, and investigate pasted nonbreaking spaces.
Decide whether you need an exact, starts-with, or contains match; add wildcards only for the latter patterns.
Check whether the criterion contains a literal wildcard that should be escaped with a tilde.
Make sure calculation is updating. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options; if the workbook is set to Manual, choose Automatic or recalculate.

Also check the formula separator used by your Excel installation. The formulas here use commas, as in the standard English examples. Depending on regional settings, Excel may require semicolons between arguments. If Excel rejects an otherwise valid formula at entry, its regional list separator may be the reason; this is different from COUNTIF failing to match text.

On Windows, F2 edits the active cell and Enter accepts the edit. On a Mac, the equivalent edit shortcut is Control+U in Excel, though keyboard settings can affect function keys. Excel for the web uses the same COUNTIF function and formula logic; its ribbon and keyboard behavior can differ from the Windows desktop app. The important diagnostic is the cell value and criterion, not the platform.

COUNTIF compared with related options

COUNTIF is the straightforward choice when one condition determines whether a cell is counted. Use COUNTIFS when every counted row must satisfy two or more conditions, such as a matching text label and a date range. For example, counting orders for one product during a period is a multi-condition task; it does not require embedding several separate COUNTIF results if the conditions apply to the same rows.

Use a filter when the main goal is to inspect or display the matching records rather than return a count. Filtering makes mismatched values visible and can reveal trailing spaces, spelling variations, or unexpected categories. A PivotTable can be more suitable for summarizing many text categories at once, especially when you need counts by each label rather than a single criterion.

These alternatives do not change the underlying text. If the source includes inconsistent spaces or spelling, COUNTIFS, filters, and PivotTables can all show results that reflect those inconsistencies. Standardizing source values is often the durable solution when the same issue affects multiple formulas or reports.

Frequently Asked Questions

Does COUNTIF work with text?
Yes. Enter the text as a quoted criterion or refer to a cell containing the criterion. COUNTIF is case-insensitive, and a plain text criterion matches the whole cell unless you use wildcards.

Why does COUNTIF return zero for text that looks the same?
The entries may contain leading or trailing spaces, nonbreaking spaces, different punctuation, or another hidden character. Compare the source and criterion carefully, and clean a copy of the data before changing the original.

How do I count cells that contain a word?
Use an asterisk before and after the word in the criterion, such as *apple* inside the COUNTIF formula. The asterisk matches any number of characters, so the rule finds the word anywhere in a cell.

Can COUNTIF distinguish uppercase from lowercase?
No. COUNTIF treats text case-insensitively. For a case-sensitive comparison, use EXACT with SUMPRODUCT, as described above.

How do I count a literal asterisk or question mark?
Use a tilde before the wildcard character in the criterion: ~* for a literal asterisk or ~? for a literal question mark. Without the tilde, Excel interprets those characters as wildcards.

Does changing a cell to Text fix COUNTIF?
Not necessarily. Formatting controls how Excel displays or treats future entries, but changing the format after data is entered does not automatically clean spaces or reliably convert existing values. Check and standardize the underlying data instead.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.