Excel COUNTIF Function — Count Cells That Meet One Condition (and the Criteria Traps)

TL;DR — COUNTIF(range, criteria) counts how many cells in range meet one condition. The whole function turns on one fact most people miss: the criteria is a piece of text — a little test written as a string. Get that, and every quirk (operators in quotes, & to inject a cell value, wildcards, the date trap) stops being mysterious.

=COUNTIF(A2:A100, "West")        ' exact text match (case-insensitive)
=COUNTIF(B2:B100, ">100")        ' operator goes INSIDE the quotes
=COUNTIF(B2:B100, ">"&E1)        ' threshold from a cell — concatenate with &
=COUNTIF(A2:A100, "*apple*")     ' contains "apple" — wildcards work on text
=COUNTIF(A2:A100, "*")           ' how many TEXT cells are there

COUNTIF is one of Excel's workhorses and one of its most-searched functions, because "how many of these match?" is a question you ask constantly. It's also where a lot of formulas quietly go wrong — almost always because the criteria wasn't built the way COUNTIF reads it.

What you'll learn

Why the criteria is a string, and what that explains
Putting operators in quotes, and injecting a cell value with &
Wildcards for "contains / starts with / ends with" text counts
The 15-digit precision trap on long numbers (card and ID numbers)
When one condition isn't enough — upgrading to COUNTIFS

The mental model: the criteria is a string, not an expression

In most formulas you write a live comparison: B2>100 evaluates as you type. In COUNTIF you don't — you hand it a description of the test as text, and COUNTIF applies that description to every cell in the range. That single design choice explains nearly everything about how COUNTIF behaves:

=COUNTIF(B2:B100, ">100")     ' correct: the whole test is a string
=COUNTIF(B2:B100, >100)       ' error: >100 isn't a valid standalone expression

Once you internalize "criteria is a string," the rules follow naturally. The operator (>, <, >=, <>) lives inside the quotes with the value. A bare number or word ("West", 42) means "equals this." And when your threshold isn't a constant but sits in a cell, you can't put the cell reference inside the quotes — you have to build the string with &.

The #1 real-world need: a threshold that lives in a cell

You almost never hard-code the number. The threshold is in a cell — a filter box, a parameter, a target. This is the single most common COUNTIF question, and the answer is concatenation:

=COUNTIF(B2:B100, ">"&E1)        ' count values greater than whatever E1 holds
=COUNTIF(A2:A100, E1)            ' exact match to E1's value
=COUNTIF(A2:A100, "*"&E1&"*")    ' cells that CONTAIN E1's text
=COUNTIF(C2:C100, ">="&E1&"")    ' works for dates in E1 too

The mechanism: ">"&E1 glues the operator string to the cell's value, producing the finished criteria string like ">100". If you ever find yourself wanting to type ">E1" — stop; that literally looks for the text "E1". The reference goes outside the quotes, joined with &.

Wildcards: contains, starts with, ends with

On text, COUNTIF supports wildcards, which is what makes it the tool for "how many cells contain…" questions:

=COUNTIF(A2:A100, "apple*")    ' starts with "apple"
=COUNTIF(A2:A100, "*apple")    ' ends with "apple"
=COUNTIF(A2:A100, "*apple*")   ' contains "apple" anywhere
=COUNTIF(A2:A100, "?????")     ' exactly 5 characters (? = one character)
=COUNTIF(A2:A100, "*")         ' any text (great for "count text cells")
=COUNTIF(A2:A100, "<>")        ' non-empty cells

Two things worth knowing. Text matching is case-insensitive — "apple" counts "Apple" and "APPLE" alike; for case-sensitive counting you need SUMPRODUCT with EXACT. And to match a literal asterisk or question mark, escape it with a tilde: "~*" finds actual * characters.

The traps: dates and long numbers

Two failure modes catch people repeatedly.

Dates. =COUNTIF(A:A, ">2026-01-01") asks COUNTIF to interpret a text date, which depends on regional settings and can silently misread. Build the criteria from a real date instead, so there's no parsing guess:

=COUNTIF(A2:A100, ">="&DATE(2026,1,1))            ' robust, locale-independent
=COUNTIF(A2:A100, ">="&DATE(2026,1,1))-COUNTIF(A2:A100, ">"&DATE(2026,12,31))

Long numbers. COUNTIF compares numbers with only 15 significant digits of precision. Credit-card numbers, 16–18-digit IDs, and long account numbers that differ only in the last digits are treated as equal, so counts come out too high. The fix is to store and match them as text and add a wildcard, which forces a text comparison: =COUNTIF(A2:A100, E1&"*") or keep the IDs as text and match the full string.

One condition only: when to reach for COUNTIFS

COUNTIF's defining limit is right in the name: one range, one criteria. The moment you need "amount > 100 and region = West," COUNTIF can't do it in one call — and stacking COUNTIFs doesn't create an and, it creates an or:

' OR (either condition): add COUNTIFs
=COUNTIF(A2:A100,"West") + COUNTIF(A2:A100,"East")

' AND (both conditions): this is COUNTIFS, not COUNTIF
=COUNTIFS(A2:A100,"West", B2:B100,">100")

So the decision rule is clean: one condition → COUNTIF; two or more conditions that must all hold → COUNTIFS. If you catch yourself writing COUNTIF(...) + COUNTIF(...) to express "and," that's the signal you've outgrown COUNTIF. (For counting distinct values rather than matches, see counting unique values.)

The judgment call

COUNTIF rewards one habit above all: treat the criteria as a string you're assembling. A constant test goes in quotes with its operator; a cell-based threshold gets built with & outside the quotes; text questions use wildcards; date questions use DATE() so nothing is left to regional parsing. And know its ceiling — a single condition. COUNTIF is deliberately narrow, and that narrowness is a feature: when your question grows a second condition, the answer isn't a cleverer COUNTIF, it's COUNTIFS.

How ExcelMaster helps

The COUNTIF that returns a wrong number rarely errors — the criteria was just built the way you read it, not the way Excel does. ExcelMaster writes the criteria correctly the first time: it concatenates your cell threshold with &, picks wildcards for "contains" questions, uses DATE() for date ranges, and switches you to COUNTIFS the moment a second condition appears. Tell it "count orders over the target in E1" or "how many rows mention refund," and it produces a formula that counts what you meant — and points out the 15-digit trap before it inflates your total.

Frequently asked questions

How do I use COUNTIF in Excel?

=COUNTIF(range, criteria) counts cells in range that meet one condition. The criteria is written as text: "West" for an exact match, ">100" for a comparison (operator inside the quotes), or "*apple*" for "contains apple". To use a value from a cell, concatenate: =COUNTIF(B:B, ">"&E1).

What's the difference between COUNTIF and COUNTIFS?

COUNTIF handles one condition on one range. COUNTIFS handles multiple conditions across multiple ranges, counting rows where all conditions hold — e.g. region = West and amount > 100. Stacking two COUNTIFs adds them (an or), so for and logic use COUNTIFS.

How do I use a cell value as the COUNTIF criteria?

Concatenate the operator string with the cell reference using &, keeping the reference outside the quotes: =COUNTIF(B:B, ">"&E1) counts values greater than E1. Writing ">E1" would search for the literal text "E1" instead.

Does COUNTIF support wildcards?

Yes, on text. * matches any number of characters, ? matches exactly one: "apple*" (starts with), "*apple*" (contains), "?????" (5 characters). Use "*" to count text cells. To match a literal * or ?, escape it with a tilde: "~*".

Why does COUNTIF miscount long numbers like credit-card IDs?

COUNTIF compares numbers with 15 significant digits of precision, so long numbers that differ only in later digits are treated as equal and over-counted. Store such IDs as text and match the full string (optionally with a wildcard) to force a text comparison.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-07.