COUNTIF Not Working With Text? Fix the Common Causes

If COUNTIF is not working with text in Excel, first check that the text in the criteria matches the cell contents and that the range is correct. For an exact match, with text values in A2:A100, use =COUNTIF(A2:A100,"Apple"); it counts cells containing Apple, regardless of capitalization.

How COUNTIF matches text

COUNTIF has two arguments: the range to examine and the criterion to apply. Text criteria go in double quotation marks when typed directly into a formula. For example, if A2:A6 contains Apple, Pear, Apple, Banana, and Apple, the formula above returns 3 when the range is A2:A6. The range and criterion must be separated by a comma in Excel for Microsoft 365 on Windows.

By default, COUNTIF compares text without distinguishing uppercase from lowercase. Apple, APPLE, and apple are therefore treated as the same text for a regular COUNTIF match. It does not, however, ignore extra spaces or other characters. A cell containing Apple followed by a space is different from a cell containing just Apple.

COUNTIF also accepts criteria that use comparison operators and wildcard characters. This is useful when you need to count text that contains a word, begins with certain characters, or fits a pattern rather than matching one complete cell value. Understanding which kind of match the formula is asking for is the fastest way to narrow down a problem.

Check the formula, range, and criterion

Start with the simple version of the formula and verify each part. If the count is zero or unexpectedly low, check these common causes before changing the data:

The range is not the data range. Confirm that the cells you want to count are inside the first argument. A formula that checks A2:A20 will not include values below row 20.
The criterion is not the text you intend. When the criterion is typed directly, put it in straight double quotation marks. A criterion written as Apple without quotation marks is treated as a name or reference, not literal text.
The row references moved. If you copied the formula, Excel may have shifted a relative range. Use dollar signs to lock a range when appropriate. With values in A2:A100, the locked-range formula is =COUNTIF($A$2:$A$100,"Apple").
The result is being mistaken for an error. A legitimate result of 0 means no cells met that criterion; it does not necessarily mean COUNTIF itself failed.

For a quick test, select a cell that visibly contains the target text and compare it with the criterion. Editing the formula and selecting the range with the mouse can also help reveal whether the reference covers the intended cells. If the workbook uses an Excel table, check that the formula refers to the table column you mean rather than a different column with similar-looking values.

Use the right kind of text criterion

An exact criterion counts cells whose whole contents match. If A2:A6 contains Apple pie, Green apple, Apple, Pineapple, and Pear, an exact Apple criterion returns 1. To count cells that contain Apple anywhere within their text, use wildcards. With those values in A2:A6, =COUNTIF(A2:A6,"*Apple*") returns 4: it matches Apple pie, Green apple, Apple, and Pineapple. The asterisk can represent any number of characters, including none.

Other useful patterns include:

With text in A2:A100, =COUNTIF(A2:A100,"Apple*") counts entries that begin with Apple, such as Apple pie.
With text in A2:A100, =COUNTIF(A2:A100,"*Apple") counts entries that end with Apple, such as Green Apple.
With text in A2:A100, =COUNTIF(A2:A100,"A?ple") matches a five-character pattern beginning with A and ending with ple, with any single character in place of the question mark.

A question mark represents one character, while an asterisk represents any number of characters. If the text you need to find includes a literal asterisk or question mark, precede that character with a tilde in the criterion. For example, with product names in A2:A100, =COUNTIF(A2:A100,"~*") counts cells whose entire value is a literal asterisk. Wildcards are powerful, but an accidental asterisk can make a formula count far more cells than expected.

Look for spaces and invisible characters

Extra spaces are a frequent reason a visible text value does not match. A cell may have a leading space, a trailing space, or multiple spaces between words. Those characters can be difficult to spot in the worksheet, but COUNTIF still sees them. Text copied from a website or another system may also contain a nonbreaking space, which looks like an ordinary space but is not removed by every basic cleanup method.

One way to inspect a suspicious cell is to compare its length with the text you expect. With the suspect value in A2, =LEN(A2) returns the number of characters, including spaces. Apple has five characters; a result of 6 can indicate an extra character. This check does not identify which character is present, but it can confirm that the contents are not as simple as they look.

For a repeatable cleanup, use a helper column rather than trying to fix each cell by hand. With imported text in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2 and fill it down. This replaces nonbreaking spaces with ordinary spaces, removes certain nonprinting characters, and trims extra ordinary spaces at the beginning and end while reducing repeated internal spaces to one. Then count the cleaned values in column B. Keep the original column if you need to preserve the source data, and review the cleaned results because cleanup can change spacing that might matter in some data.

Check whether the criterion comes from a cell

A criterion can be typed directly into the formula or taken from another cell. If D2 contains Apple, with text values in A2:A100, use =COUNTIF(A2:A100,D2). Do not put quotation marks around the cell reference: writing “D2” would search for the literal text D2 rather than use the value in D2.

If you want to add wildcards around a criterion stored in D2, join the asterisks to the cell value with ampersands. With text in A2:A100 and the search word in D2, =COUNTIF(A2:A100,"*"&D2&"*") counts cells containing the word anywhere. The ampersand joins text pieces into one criterion. This is a common fix when a formula works with a typed word but not with a reference, or when it needs to search for a changing term.

When a criterion includes a comparison operator, combine the operator and a cell reference rather than placing the whole expression in quotation marks. For example, COUNTIF can compare numeric values using a criterion such as greater than a value in D2. This matters when a formula is built from a cell and the operator seems to be ignored. For simple text counts, however, use a direct text match or wildcard pattern rather than adding comparison symbols without a specific reason.

Distinguish text numbers from ordinary text

Some cells that look like words or codes are actually numbers stored as text, while others are numeric values displayed with a custom format. This often happens with imported identifiers, postal codes, and entries that contain leading zeros. COUNTIF can count text criteria, but a mismatch between the underlying value and the criterion can make results confusing. For instance, a code such as 00125 may be stored as text to preserve its zeros; converting it to a number could change its meaning or display.

Check the cell contents and formatting before converting anything. A green triangle may indicate that a number is stored as text, though its presence depends on Excel’s error-checking settings. The formula bar can also help reveal the stored value. If the entries are identifiers, preserving them as text is often appropriate; if they are genuinely quantities, consistent numeric values may be better. Avoid bulk conversion until you know which type the data is supposed to represent.

Also confirm that you have not confused a blank cell with text that looks blank. A formula may return an empty string, or a cell may contain spaces, and those are not always equivalent to a genuinely empty cell. If a COUNTIF criterion is meant to find blank-looking entries, inspect representative cells and decide whether you need truly empty cells or cells whose displayed result is empty.

When COUNTIF is not the right comparison

COUNTIF is not case-sensitive. If you need to count Apple but exclude apple, COUNTIF alone cannot distinguish them. With values in A2:A10, =SUMPRODUCT(--EXACT(A2:A10,"Apple")) returns the number of case-sensitive exact matches; for example, it returns 2 if the range has two cells containing Apple with that exact capitalization and any lowercase versions are separate.

For several separate conditions, use COUNTIFS rather than trying to combine unrelated criteria in one COUNTIF. For example, a worksheet might need to count rows where a category is Apple and a status is Active. COUNTIFS evaluates multiple criteria ranges together, with each range paired with its criterion. Make sure the paired ranges have the same size, just as you should with COUNTIF’s range.

If the value appears to be present but a wildcard count still returns zero, check for spelling differences, punctuation, and unusual spaces. If a normal COUNTIF returns a number that is too high, narrow the criterion: an exact match may be more suitable than a pattern such as *app*. A broad wildcard can match words you did not intend, including words that merely contain the same letter sequence.

A practical troubleshooting sequence

Use this order to identify the cause without making unnecessary changes to the worksheet:

Write a simple exact-match COUNTIF using a small range that includes the example cell.
Check whether the criterion is entered as quoted text or as a reference to the correct cell.
Decide whether you need an exact match, a starts-with or ends-with match, or a contains match.
Inspect a cell that should match for leading spaces, trailing spaces, punctuation, and hidden characters.
Check whether the data is consistently text or numeric, especially for imported codes and values with leading zeros.
Expand the range only after the small test works, and lock the range if you will copy the formula.

This sequence separates formula problems from data problems. If the small test works but the full formula does not, the larger range or copied references are likely worth checking. If the criterion works for one cell but not another that looks identical, inspect the underlying contents of the second cell before changing the formula.

Frequently Asked Questions

Why does COUNTIF return 0 when the text is visibly in the cell?
Most often, the cell contains an extra space, a hidden character, punctuation, or a slightly different spelling. Confirm the range and criterion first, then inspect the cell contents or test its character length. A wildcard such as *word* can help determine whether the text is present as part of a longer entry.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text matching is case-insensitive, so Apple and APPLE match the same criterion. If capitalization must count as a difference, use a case-sensitive approach such as SUMPRODUCT with EXACT.

How do I count cells that contain a word anywhere?
Put an asterisk on both sides of the word in the criterion. With entries in A2:A100, =COUNTIF(A2:A100,"*apple*") counts cells containing apple anywhere in their text, without regard to capitalization.

Can I use a cell reference as the COUNTIF criterion?
Yes. If the search term is in D2, use D2 as the second argument without quotation marks. To search for the referenced term within longer text, join asterisks to the reference with ampersands.

What is the difference between COUNTIF and COUNTIFS?
COUNTIF applies one condition to one range. COUNTIFS can apply multiple conditions to corresponding ranges, such as a category and a status in separate columns. Use COUNTIF for a single text test and COUNTIFS when the row must meet more than one condition.

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.