TL;DR —
COUNTIFScounts the rows that pass every condition. Syntax:=COUNTIFS(range1, criteria1, range2, criteria2, …). The danger isn't an error message — it's a plausible wrong number. A bad criteria still returns a count, just not the one you wanted. The two rules that prevent it: operators live inside quotes (">100","<>0"), and to compare against a cell you concatenate —">"&A1, never">A1". Multiple pairs are joined with AND; for OR you sum an array. And the function people most often misuse it for — counting distinct values — isn't its job: useCOUNTA(UNIQUE(…))instead. Works in Excel 2007+.
=COUNTIFS(Status, "Open")
=COUNTIFS(Status, "Open", Priority, ">"&A1)
COUNTIFS is the tally sibling of SUMIFS: same
conditional-aggregation engine, but it counts rows instead of adding a column.
That makes it the workhorse for "how many of X meet Y" — and it's uniquely
treacherous, because when you get the criteria wrong it doesn't complain. It just
hands you a number, and numbers look like answers.
What you'll learn
- The mental model:
COUNTIFScounts the rows a filter would keep - Why a wrong criteria is silent — and the
">"&A1rule that fixes the common one COUNTIFvsCOUNTIFS, and why the plural scales- AND is built in; OR you build with an array
- Counting between two dates, and counting blanks vs non-blanks
- Why distinct counts need
UNIQUE, notCOUNTIFS
The mental model: count the rows a filter would keep
Picture the rows that FILTER would return for a
set of conditions — COUNTIFS just tells you how many there are without
showing them. You name a column and a test, stack up more pairs, and Excel counts
the rows where all the tests pass. Like SUMIFS, every range must be the
same height; unlike SUMIFS, there's no "value" column — the count is the
answer.
' How many open tickets?
=COUNTIFS(Status, "Open")
' How many open AND high-priority? (both must be true)
=COUNTIFS(Status, "Open", Priority, "High")
The trap that makes COUNTIFS dangerous: silent wrong answers
SUMIFS returning the wrong column at least often produces a zero or a number
that's obviously off. COUNTIFS is worse: a bad criteria returns a believable
count. The most common cause is comparing against a cell the wrong way:
=COUNTIFS(Priority, ">"&A1) ' ✅ "greater than the value in A1"
=COUNTIFS(Priority, ">A1") ' ❌ counts cells literally equal to the text "A1" → 0
">A1" is a string. Excel looks for cells whose text is exactly >A1, finds
none, and returns 0 — or worse, a partial match that's plausibly close. The rule
is identical to SUMIFS: the operator (>, <, >=, <>) stays inside
the quotes; the cell reference goes outside, joined with &. Because the
failure is silent, build the habit of testing one condition at a time and
sanity-checking the count against a visible filter.
A second silent surprise: text matching is case-insensitive, so
COUNTIFS(Name, "apple") also counts "APPLE" and "Apple". If you genuinely need
case-sensitive counting, COUNTIFS can't do it — use
SUMPRODUCT(--EXACT(range, "apple")) instead.
COUNTIF vs COUNTIFS: just use the plural
COUNTIF takes one condition; COUNTIFS takes many, joined with AND. Unlike the
SUMIF/SUMIFS pair, the argument order is the same here — so there's no
silent-swap landmine. But the recommendation still holds for a simpler reason:
the moment you need a second condition, COUNTIF can't grow, and you rewrite the
formula. Start with COUNTIFS and adding the next criteria is just two more
arguments. One function to remember, one that scales.
AND is free; OR needs an array
Stacked pairs mean AND. To count rows matching any of several values in one
column, sum a COUNTIF over an array of those values:
' AND — Open AND High
=COUNTIFS(Status, "Open", Priority, "High")
' OR — Open OR Pending OR Blocked
=SUM(COUNTIF(Status, {"Open","Pending","Blocked"}))
The array form returns one count per value and SUM totals them. This is safe
for a single column because a row can only hold one status. Watch out when
your OR spans different columns or overlapping conditions — there, adding
counts double-counts rows that satisfy more than one term, and you're better off
with FILTER and COUNTA on the result.
Counting dates, blanks, and non-blanks
A date range is two tests on the date column — and as with SUMIFS, build the
boundaries with DATE() so they don't shift between locales:
=COUNTIFS(OrderDate, ">="&DATE(2026,1,1), OrderDate, "<="&DATE(2026,3,31))
For emptiness, the criteria are short but easy to confuse:
=COUNTIFS(Notes, "") ' truly empty cells
=COUNTIFS(Notes, "<>") ' non-blank cells (any content)
One caveat: a cell holding a formula that returns "" looks blank but isn't
empty — COUNTIFS(…, "") and COUNTBLANK will disagree about it. If your "blank"
cells are formula results, count them with the "<>" complement instead.
The misuse to unlearn: distinct counts
The single most common wrong reach for COUNTIFS is "count how many different
customers we have." COUNTIFS counts rows, not distinct values — it has no idea
what "unique" means. The modern answer is one short formula:
=COUNTA(UNIQUE(Customer)) ' how many distinct customers
=COUNTA(UNIQUE(FILTER(Customer, Region="West"))) ' distinct customers in the West
UNIQUE collapses the list to distinct values and
COUNTA counts them — optionally filtered first. If you find yourself building a
helper column of 1/COUNTIF(…) fractions to fake a distinct count (the classic
pre-365 hack), that's the signal to switch to COUNTA(UNIQUE()).
How ExcelMaster helps
"How many overdue invoices over the limit in A1, this quarter?" is three criteria
and a date range — and the part that bites is ">"&A1 versus ">A1".
ExcelMaster writes the COUNTIFS from your plain-English question with the
concatenation and DATE() bounds correct, and if you're really after a distinct
count it reaches for COUNTA(UNIQUE()) instead — catching the misuse before it
becomes a wrong number in a report.
Frequently asked questions
Why does COUNTIFS return 0 when I reference a cell?
You almost certainly wrote ">A1" instead of ">"&A1. With the cell reference
inside the quotes, Excel searches for the literal text "A1" and finds nothing.
Put the operator in quotes and concatenate the cell: =COUNTIFS(range, ">"&A1).
What's the difference between COUNTIF and COUNTIFS?
COUNTIF takes a single condition; COUNTIFS takes one or more, combined with
AND. The argument order is the same in both, so the safe habit is to always use
COUNTIFS — it scales the moment you need a second test.
How do I count with OR / multiple values in one column?
Sum a COUNTIF over an array of the values:
=SUM(COUNTIF(Status, {"Open","Pending"})). This counts rows matching any of
them without double-counting, because a row holds only one status.
How do I count values between two dates?
Two criteria on the date column, built with DATE():
=COUNTIFS(OrderDate, ">="&DATE(2026,1,1), OrderDate, "<="&DATE(2026,3,31)).
How do I count unique (distinct) values?
Not with COUNTIFS — it counts rows. Use =COUNTA(UNIQUE(range)), and filter
first if you need distinct values within a subset:
=COUNTA(UNIQUE(FILTER(Customer, Region="West"))).
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-24.
Related guides: Excel SUMIFS · Excel AVERAGEIFS · Excel UNIQUE · Excel FILTER