Excel COUNTIF Function

COUNTIF counts the number of cells in a range that meet a single condition you specify. It works with numbers, text, dates, and wildcards, and it's one of the first functions worth learning past SUM. The most common mistake: forgetting quotes around text or symbol criteria, which returns 0 instead of an error, so the mistake goes unnoticed.

Syntax

=COUNTIF(range, criteria)

Parameter	Required	Description
range	Yes	The cells you want to check. Can be a single column, a row, or a full table range.
criteria	Yes	The condition a cell must match to be counted. Numbers can go in directly (100); text, symbols, and cell references generally need quotes (">100", "Chicago").

Basic Example

You're managing a list of 200 customer orders in column C, and you want to know how many came from Chicago.

=COUNTIF(C2:C201, "Chicago")

// C2:C201  — the range holding each order's city
// "Chicago"  — the exact text COUNTIF is matching against

COUNTIF checks every cell in C2:C201, compares it to "Chicago", and returns the total number of matches. Change the text and the count updates automatically — no need to touch the range.

How COUNTIF Works

It only handles one condition

COUNTIF evaluates a single criterion against a single range. Need to count orders from Chicago and over $500? That's two conditions, and COUNTIF can't do it. Use COUNTIFS instead.

Text matching isn't case-sensitive

"chicago", "Chicago", and "CHICAGO" all count as the same match. If you need a case-sensitive count, you'll need an array formula with EXACT — COUNTIF alone won't get you there.

Numbers and text as criteria behave differently

Type a number directly: =COUNTIF(A2:A100, 50). No quotes needed. Type a comparison, text, or a cell reference used inside a comparison, and it needs quotes: =COUNTIF(A2:A100, ">50"), =COUNTIF(A2:A100, "Complete"), =COUNTIF(A2:A100, ">"&B1).

Wildcards work in text criteria

* matches any number of characters, ? matches exactly one. =COUNTIF(A2:A100, "*east*") counts any cell containing "east" anywhere in the text — "Northeast," "Easton," "Southeasterly," all of it.

Blank cells aren't automatically excluded

=COUNTIF(A2:A100, "<>") counts non-blank cells specifically. A plain =COUNTIF(A2:A100, ">0") will skip blanks on its own, but only because blanks fail the numeric comparison — not because COUNTIF ignores them by default.

Counting With Comparisons and Wildcards

Comparison operators go inside quotes, as text, even though they're evaluating numbers:

=COUNTIF(scores, ">=90")   // count of scores 90 or above
=COUNTIF(scores, "<60")    // count of scores below 60
=COUNTIF(scores, "<>0")    // count of everything except exactly zero

To build a comparison from a cell instead of a hardcoded number, concatenate the operator with &:

=COUNTIF(scores, ">"&D1)   // D1 holds the passing threshold

This is worth setting up once. Hardcoding 90 into a formula means editing the formula every time the threshold changes. Pointing at D1 means changing one cell.

Wildcards need care around real symbols. If your data actually contains an asterisk or a question mark and you want to match it literally, put a tilde in front: "*~?*" matches any text containing a literal question mark.

Common Use Cases

Count how many times a value appears

You have 500 support tickets in column B tagged by category, and you want to know how many are tagged "Billing."

=COUNTIF(B2:B501, "Billing")   // total tickets tagged Billing

Count cells above or below a threshold

A commission tracker needs to know how many reps hit at least $10,000 in sales this month.

=COUNTIF(sales_totals, ">=10000")

// sales_totals  — named range covering each rep's monthly total
// ">=10000"     — the threshold, written as text with the operator

Count entries within a date range

Counting orders placed in Q1 needs two COUNTIF calls, since a single COUNTIF can't express a range on its own:

=COUNTIF(order_dates, ">="&DATE(2026,1,1)) - COUNTIF(order_dates, ">"&DATE(2026,3,31))

Each COUNTIF returns a running total from one boundary; subtracting the second from the first isolates the window between them. If that feels clunky, it is — this is exactly the kind of two-condition problem COUNTIFS handles in one function call.

Count partial text matches

You're cleaning a product list where "East" region entries were typed inconsistently — "East," "Eastern," "NE-East" — and you want a single count regardless of formatting.

=COUNTIF(regions, "*East*")

Common Mistakes and Wrong Results

COUNTIF rarely throws an error. It's far more likely to return a number that's simply wrong, which is why checking the result against what you'd expect matters more than watching for red error text.

Why does COUNTIF return 0 when I know there are matches?

The usual cause is a mismatch between the criteria text and the actual cell content — extra spaces, a typo, or numbers stored as text when your criteria expects a real number. Wrap the range in TRIM to check for stray spaces, or use =ISNUMBER(A2) on a sample cell to confirm the data type matches what your criteria assumes.

Why is COUNTIF counting more than expected?

Wildcards are the usual suspect. "*east*" matches "Easton," "Southeast," and "Least" alike. If you only want a whole-word match, an unadorned "East" (no asterisks) requires an exact match rather than a substring.

Does COUNTIF work with dates?

Yes, but the criteria has to be built the same way as a number comparison: =COUNTIF(dates, ">="&DATE(2026,1,1)). Typing a date directly as text, like ">1/1/2026", is unreliable across regional date formats — use the DATE function to avoid ambiguity.

What happens if the range contains blank cells?

Blanks are counted like any other cell unless your criteria excludes them. "<>" counts every non-blank cell; leaving criteria open-ended (like ">0") filters out blanks incidentally, only because they fail that specific comparison.

Known Limitations

Limitation	Workaround
Only one condition per formula	COUNTIFS for multiple conditions
Not case-sensitive	SUMPRODUCT with EXACT
No native "between two dates" criteria	Two COUNTIF calls subtracted, or COUNTIFS
Criteria syntax (quotes vs. no quotes) is inconsistent and easy to get wrong	Build comparisons with & concatenation instead of typing them by hand

When to Use COUNTIFS Instead

The moment you need a second condition, COUNTIF stops being an option. COUNTIFS takes the same basic idea and extends it to as many range/criteria pairs as needed:

// COUNTIF — one condition
=COUNTIF(sales_totals, ">=10000")

// COUNTIFS — two conditions: sales over $10,000 AND region is West
=COUNTIFS(sales_totals, ">=10000", regions, "West")

There's no real downside to COUNTIFS even for a single-condition count — it behaves identically to COUNTIF when given one range/criteria pair. Some people standardize on COUNTIFS everywhere for that reason, so they never have to switch functions mid-project.

Related Functions

Function	Use this when...
COUNTIFS	You need to match two or more conditions at once.
SUMIF	You need to add values, not count cells.
COUNTA	You just need a count of non-blank cells, no condition.
COUNT	You only need to count cells containing numbers.
VLOOKUP	You need to pull a specific value tied to a match, not just count how many matches exist.