Fixing Excel COUNTIF That Returns Wrong Count With Wildcard Criteria

You write a COUNTIF formula, drop in a wildcard, and the count comes back completely wrong — too high, too low, or stubbornly zero. The formula looks correct, and yet Excel is counting things you didn't ask it to count, or missing things that are clearly there.

Wildcard characters in COUNTIF follow a specific set of rules that are easy to get wrong. Once you understand how Excel reads those characters, the fix is usually a one-character change.

What You'll Learn

• How Excel interprets *, ?, and ~ inside COUNTIF criteria
• Why your asterisk might be matching cells you never intended
• How to count cells that literally contain an asterisk or question mark
• How wildcard rules carry over to COUNTIFS across multiple columns
• The edge cases that trip up even experienced spreadsheet users

Why COUNTIF and Wildcards Are a Tricky Pair

COUNTIF is designed to count cells that meet a condition. That condition can be an exact value, a comparison, or a pattern. Patterns use wildcard characters, and that flexibility is what creates confusion — because Excel treats certain characters as special instructions rather than literal text.

The issue compounds when your source data actually contains asterisks or question marks (think part numbers, product codes, or imported data from external systems). Suddenly the character you want to match literally is the same character Excel uses to mean "match anything."

How Excel Interprets Wildcard Characters in Criteria

Excel recognizes exactly three wildcard characters in COUNTIF criteria strings:

• * — matches any sequence of characters, including zero characters
• ? — matches any single character
• ~ — an escape prefix that turns the next character into a literal

These rules apply in the criteria argument of COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, and several lookup functions. They do not apply inside formulas like EXACT or when you compare values with = operators in array formulas.

It is also worth noting that wildcards only work on text values. If your cells hold numbers, dates, or error values, the wildcard criteria will return zero regardless of what you type.

The Asterisk Problem: Matching More Than You Intend

The most common source of inflated counts is an asterisk that matches far more cells than expected. Consider this: if your criteria is just "*", COUNTIF will count every non-empty text cell in the range. That includes cells you had no intention of counting.

=COUNTIF(A2:A100, "*")     ' counts ALL non-empty text cells
=COUNTIF(A2:A100, "*Q3*")  ' counts cells that contain Q3 anywhere
=COUNTIF(A2:A100, "Q3*")   ' counts cells that START with Q3

The second formula looks like it should only count cells with "Q3" in them — and it does. But users often expect "containing Q3" to behave exactly like "equal to Q3", which is not the case. A cell containing "Pre-Q3 Results" will match "*Q3*" but not "Q3".

If you want an exact match, do not use wildcards at all. Just pass the text directly:

=COUNTIF(A2:A100, "Q3")    ' exact match only

If you are building the criteria from a cell reference — say the value is in D1 — wrap it without any wildcard characters unless you specifically need partial matching:

=COUNTIF(A2:A100, D1)      ' exact match from cell reference

To do a contains-match from a cell reference, concatenate the wildcards explicitly:

=COUNTIF(A2:A100, "*"&D1&"*")  ' partial match around D1's value

This is a common source of bugs: a cell reference that already contains an asterisk gets passed directly to COUNTIF, which then double-wildcards the match.

The Question Mark Problem: One Character Means Any Character

The question mark wildcard matches exactly one character — any character. If you are trying to count cells that literally contain a question mark (a common pattern in survey data or user-generated content), you will get a wrong count because Excel reads the ? as an instruction.

For example, suppose column A contains these values:

Status?
Status1
Status2
Status!

This formula:

=COUNTIF(A2:A5, "Status?")

Returns 4, not 1. Every cell matches because each has exactly one character after "Status". That is almost certainly not what you wanted.

The pattern-matching behavior of ? is also what causes confusion when filtering data with codes like A-?01 or part numbers with variable middle segments. Excel reads every ? as "anything goes here."

The Tilde Fix: Escaping Literal Asterisks and Question Marks

To match a literal asterisk or question mark, prefix it with a tilde (~). The tilde tells Excel to treat the next character as plain text, not a wildcard.

=COUNTIF(A2:A100, "Status~?")   ' counts cells equal to "Status?"
=COUNTIF(A2:A100, "~*Q3~*")     ' counts cells equal to "*Q3*"
=COUNTIF(A2:A100, "*~**")       ' counts cells that CONTAIN a literal *

The last formula is worth unpacking. It reads: match any prefix (*), then a literal asterisk (~*), then any suffix (*). That counts every cell that contains at least one asterisk somewhere in its text.

What if the tilde itself is in your data? Escape it with another tilde:

=COUNTIF(A2:A100, "~~")         ' counts cells equal to "~"
=COUNTIF(A2:A100, "*~~*")       ' counts cells that contain a literal ~

When building criteria dynamically from a cell reference, you need to substitute the special characters before passing them in. The SUBSTITUTE function is the cleanest way to do this:

=COUNTIF(A2:A100,
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(D1, "~", "~~"),
        "*", "~*"),
    "?", "~?"))

Apply the tilde substitution first; otherwise you will accidentally escape the tildes you just added. This three-level SUBSTITUTE chain sanitizes any value in D1 so it is treated as a literal string rather than a pattern.

Case-Sensitivity and Wildcard Behavior

COUNTIF is case-insensitive. The criteria "*apple*" matches "Apple", "APPLE", and "apple" equally. If you need case-sensitive counting, COUNTIF cannot do it natively — you need a SUMPRODUCT formula with EXACT:

=SUMPRODUCT((EXACT(A2:A100, "Apple"))*1)

Note that EXACT does not support wildcards, so this approach only works for exact matches. For case-sensitive partial matching, you would need to combine EXACT with MID or FIND in a more complex array formula, which is outside the scope of a wildcard fix.

One subtlety: accented characters. COUNTIF treats "café" and "cafe" as different values, but wildcard patterns do not normalize Unicode. If your data comes from multiple locales, normalizing the text before counting is safer than relying on wildcard matching.

Using COUNTIFS With Wildcard Criteria Across Multiple Columns

COUNTIFS applies the same wildcard rules to every criteria argument. Each criteria range and criteria pair is evaluated independently, and all conditions must be true for a row to be counted.

=COUNTIFS(A2:A100, "*Q3*", B2:B100, "Approved")

This counts rows where column A contains "Q3" anywhere and column B equals exactly "Approved". The wildcard applies only to the criteria it belongs to.

A subtle COUNTIFS gotcha: all criteria ranges must be the same size and shape. If they are not, you get a wrong count or an error. For a deeper look at how misaligned ranges cause double-counting in similar functions, the article on fixing Excel SUMIFS that double-counts rows when ranges are misaligned covers the exact same geometry rule in detail.

You can mix wildcard and non-wildcard criteria freely across the argument pairs:

=COUNTIFS(
    A2:A100, "*~**",   ' column A contains a literal asterisk
    B2:B100, ">0",     ' column B is a positive number
    C2:C100, "?")      ' column C has exactly one character

Common Pitfalls and Edge Cases

Numbers stored as text

Wildcards only match text cells. If a number like 1234 is stored as the number data type, "*1234*" returns zero. You will need to either convert the numbers to text first (using TEXT or an apostrophe prefix) or use a different approach entirely. Check for the green triangle in the cell corner — that usually signals a number stored as text, which wildcards will match. A cell with no triangle holds a real number, and wildcards will not touch it.

Empty string criteria behaves like a wildcard

Passing an empty string "" as criteria counts cells that are empty — it does not use wildcard logic. But "*" counts cells that are non-empty text. These are opposites, and confusing them explains many accidental wrong-direction counts.

Leading or trailing spaces in the data

If a cell contains " Apple" (with a leading space), the criteria "Apple" will not match it, but "*Apple*" will. This can inflate counts without you realizing it. The issue of trailing spaces causing lookup mismatches is the same root problem — stray whitespace that wildcard matching inadvertently papers over.

Criteria from another formula result

When your criteria argument is a formula result — like TEXT(TODAY(), "mmm") — the result is a plain string, and any special characters in it are still interpreted as wildcards. If your formula output could ever produce an asterisk or question mark, sanitize it with SUBSTITUTE before passing it to COUNTIF.

Structured table references

COUNTIF works with structured references like Table1[Status], and the wildcard rules apply identically. There is no special behavior to worry about here, but confirm the column reference resolves to the correct range size when the table is filtered.

Formula errors hiding real issues

If COUNTIF returns a number but it looks suspiciously round or suspiciously large, do not assume the formula is wrong. Check whether IFERROR is wrapping any upstream formulas that feed the criteria range — a hidden error in the range can silently alter what gets counted. The article on fixing IFERROR that hides real errors and returns wrong results explains exactly how that masking behavior works.

Wrapping Up: Next Steps

Wildcard mismatches in COUNTIF almost always come down to one of three things: an unintended asterisk matching too broadly, an unescaped literal special character in the data, or a number stored as text that wildcards cannot touch. Once you identify which case you're in, the fix is mechanical.

Here are four concrete actions to take right now:

1. Audit your criteria string. Print the criteria to a blank cell using a formula like ="*"&D1&"*" and read it literally — every * and ? in there will be treated as a wildcard.
2. Escape special characters in dynamic criteria. If the value feeding your criteria can ever contain *, ?, or ~, apply the three-level SUBSTITUTE chain shown above before passing it into COUNTIF.
3. Verify data types in the counted range. Select the range, open Format Cells, and check whether the cells you expect to match are actually stored as text. Use the VALUE or TEXT function to convert if needed.
4. Test with a known small dataset. Paste five rows into a scratch sheet with controlled values, one of which clearly should match and one of which clearly should not. Confirm the formula behaves correctly before scaling up.
5. Replace COUNTIF with COUNTIFS when you need a second condition to narrow the count — the syntax is identical but gives you the extra guardrail of a corroborating criteria column.