COUNTIF and COUNTIFS: How to Count What Matters

COUNTIF counts cells that meet one condition. COUNTIFS counts cells that meet several.

=COUNTIF(A2:A100, "Riyadh")

=COUNTIFS(A2:A100, "Riyadh", B2:B100, "Hardware")

Good news if you've just wrestled with SUMIF and SUMIFS: the argument order doesn't flip here. Adding the S just lets you keep adding range-and-criteria pairs. There's no separate "count range", because you're counting the very cells you're testing.

First, which COUNT do you need?

Excel has five counting functions and people reach for the wrong one constantly.

Function	Counts
COUNT	Cells containing numbers only
COUNTA	Cells that aren't empty — text, numbers, errors
COUNTBLANK	Empty cells
COUNTIF	Cells meeting one condition
COUNTIFS	Cells meeting several conditions

The usual mistake is COUNT on a column of names, which returns 0 because there are no numbers in it. COUNTA is what that job needs.

COUNT returns zero on a column of text — COUNTA is the one that counts names.

1. Counting one value

=COUNTIF($A$2:$A$100, "Riyadh")

How many rows are from Riyadh. Lock the range with dollar signs before dragging the formula down a summary list, or the range slides and every total below the first is wrong.

2. Counting above a threshold

=COUNTIF($C$2:$C$100, ">1000")

The operator goes inside the quotes, exactly as it does in SUMIF. And the boundary rule is the same one that catches people out in IF formulas: ">1000" excludes exactly 1000, ">=1000" includes it.

3. Two conditions at once

=COUNTIFS($A$2:$A$100, "Riyadh", $B$2:$B$100, "Hardware")

Every pair must be satisfied for a row to count — the conditions combine with AND, never OR. For OR, add two COUNTIFS together:

=COUNTIFS($A$2:$A$100, "Riyadh") + COUNTIFS($A$2:$A$100, "Jeddah")

⚠️ If a row could match both conditions, adding them double-counts it. That's not a risk when you're counting mutually exclusive categories like regions, but it is when the conditions overlap.

4. Counting within a date range

=COUNTIFS($D$2:$D$100, ">=2026-01-01", $D$2:$D$100, "<=2026-03-31")

The same column named twice, once for each end of the window. This is how you count anything per month or per quarter.

5. Criteria that live in a cell

A plain reference needs nothing special:

=COUNTIF($A$2:$A$100, F1)

An operator plus a reference must be joined with &:

=COUNTIF($C$2:$C$100, ">"&F1)

">F1" inside quotes is literal text, not a reference. The formula returns 0 and gives no hint why.

6. Partial text

=COUNTIF($A$2:$A$100, "*steel*")

* matches any number of characters, ? matches exactly one. So "North*" counts everything starting with North, and "PRD-1??" counts codes with exactly two characters after PRD-1.

7. Blanks and non-blanks

=COUNTIF($B$2:$B$100, "<>")

=COUNTBLANK($B$2:$B$100)

⚠️ These two disagree about cells holding a formula that returns "". COUNTBLANK counts them as blank; COUNTA and "<>" treat them as filled. If your two numbers don't add up to the row count, empty-string formulas are the reason.

8. Finding duplicates

The most useful thing COUNTIF does isn't counting at all — it's flagging repeats:

=COUNTIF($A$2:$A$100, A2)

Fill it down and every row shows how many times its value appears. Anything above 1 is a duplicate. Filter on that column and you can see exactly what a cleanup would remove — before removing anything.

For a running count that numbers each occurrence 1, 2, 3, expand the range as it goes:

=COUNTIF($A$2:A2, A2)

The first reference is locked, the second isn't. That's what makes the range grow as you fill down.

Traps

Trailing spaces

"Riyadh " isn't "Riyadh", so it isn't counted. A count that's lower than you expected usually means whitespace, not missing data.

Numbers stored as text

">1000" can't compare against text. The formula returns 0 with no error.

Case is ignored

"riyadh", "Riyadh" and "RIYADH" all count together. For case-sensitive counting:

=SUMPRODUCT(--EXACT($A$2:$A$100, "Riyadh"))

Long numbers count as equal

COUNTIF compares only the first 15 digits of a number. Two different 18-digit account or ID numbers are counted as the same value. Store long identifiers as text, or compare with SUMPRODUCT and EXACT instead. This one is genuinely surprising and it produces confidently wrong answers.

Ranges of different sizes

Every range in a COUNTIFS must be the same height, or you get #VALUE!.

Which one to use

Question	Formula
How many rows have data?	COUNTA
How many are numbers?	COUNT
How many match one thing?	COUNTIF
How many match several?	COUNTIFS
How many are duplicated?	COUNTIF filled down
Case-sensitive counting	SUMPRODUCT with EXACT

Does this work in Google Sheets?

Yes — same names, same arguments, same wildcards. The 15-digit limitation applies there too.

Common questions

What's the difference between COUNT and COUNTA?

COUNT only sees numbers. COUNTA sees anything that isn't empty. On a column of names, COUNT returns 0.

Can I count unique values?

Not with COUNTIF alone. On Microsoft 365: =COUNTA(UNIQUE(A2:A100)). On older versions: =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)), which fails if the range contains blanks.

Why does my count include rows I filtered out?

COUNTIF ignores filters entirely. Use SUBTOTAL or AGGREGATE to count only visible rows.

Can COUNTIF count colour-filled cells?

No. Cell colour isn't data. Add a column recording whatever the colour represents, then count that — which is worth doing anyway, because colour alone can't be sorted, filtered reliably, or audited.

The short version

• COUNTIF and COUNTIFS use the same argument order — no flip to remember.
• Operators go inside the quotes; cell references need &.
• Conditions are always AND. For OR, add two together and watch for overlap.
• COUNT is numbers only; COUNTA is anything non-empty.
• =COUNTIF($A$2:$A$100, A2) filled down is the fastest duplicate finder in Excel.
• Numbers longer than 15 digits count as identical — store IDs as text.

Counting and totalling cover most summary work. When a sheet needs dozens of them across many groupings, that's the point where a pivot table replaces the lot.