Featured image
COUNTIFS in Excel
COUNTIFS counts the rows that satisfy every one of several conditions: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …). Unlike SUMIF and SUMIFS, it takes its arguments in the same order as COUNTIF, so there is nothing to unlearn. Every pair must hold on the same row for that row to count.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
COUNTIFS is COUNTIF with more conditions, and it is the function to reach for whenever a question has the shape how many rows are both this and that. How many North-region reps beat target. How many orders in March were still unpaid.
It is also the companion to check a SUMIFS against. When a total looks too low, the same criteria passed to COUNTIFS tell you how many rows actually matched, which separates a wrong criterion from a correct criterion matching fewer rows than you thought.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The first column to test.
- What counts as a match in that column. "North" for text, 12 for a number, ">10" for a comparison with the operator inside the quotes, ">"&C2 against a cell, "A*" for a wildcard.
- optional
- Further pairs. Every range must be the same height as the first, and a row counts only when all of the pairs pass on that row.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
A category and a threshold
=COUNTIFS(B2:B7, "South", C2:C7, ">10")Returns 1
Two reps are in the South, Blake and Eli, and only Eli's 15 units clear ten. Both conditions have to hold on the same row, which is why the answer is 1 rather than the three rows that satisfy one condition or the other.
Two conditions on different columns
=COUNTIFS(F2:F7, "A", C2:C7, ">=12")Returns 2
Grade A and at least twelve units. Ada has exactly 12 and is counted, because the test is >= rather than >. That boundary is where a count most often comes out one short.
What a blank does to the count
=COUNTIFS(B2:B7, "East", C2:C7, ">0")Returns 1
Two rows are in the East, but Fay's units cell is empty and a blank is not greater than zero, so only Dara is counted. Compare it with =COUNTIFS(B2:B7, "East"), which returns 2: the difference between the two is exactly the rows with missing data.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Every condition must hold on the same row
The pairs are joined by and. Two criteria against the same column therefore count nothing, because no row can hold two different values at once.
=COUNTIFS(B2:B7, "North") + COUNTIFS(B2:B7, "South")Returns 4. For or, add separate COUNTIFS together. Beyond a few alternatives, a helper column flagging the rows you want is easier to read and to check.
Using COUNTIFS to check a SUMIFS
A SUMIFS that returns less than expected gives no clue whether the criteria are wrong or the matching rows are simply fewer than you assumed. COUNTIFS with the same criteria answers that directly.
- 1Copy the SUMIFS into a spare cell and change it to COUNTIFS, dropping the sum range.
- 2Compare the count with the number of rows you believe should match.
- 3If the count is lower, delete criteria pairs one at a time until it jumps. The pair you removed is the one excluding rows.
- 4If the count is right and the total still looks wrong, the problem is in the column being added, usually numbers stored as text.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=COUNTIFS(B2:B7, "North")/COUNTIFS(B2:B7, "West")WhyOne count expressed as a proportion of another, where the second counted nothing.
FixCheck the criterion in the denominator before hiding the error. An empty category and a mistyped one produce exactly the same #DIV/0!, and only one of them is acceptable.
=COUNTIFS(B2:B7, "East") + "x"WhyCOUNTIFS returns 0 rather than erroring when nothing matches, so an error here comes from the surrounding formula. In Excel it also appears when the criteria ranges are not all the same height.
FixCheck that every range ends on the same row, then evaluate the COUNTIFS alone to confirm it returns a number.
What goes wrong
These are the mistakes that come up again and again with COUNTIFS. Each one is reasonable. That is why people make it.
Expecting two criteria on one column to mean or.
WhyNorth and South reads like two things you want counted.
InsteadEach pair must hold on the same row, so that combination counts nothing. Add two COUNTIFS together.
Leaving the quotes off a text criterion.
WhyIt reads perfectly well without them.
InsteadQuote it: "South". Excel answers an unquoted word with #NAME?; the grid here answers it with zero, which is harder to notice and just as wrong.
Assuming blanks behave like zeros.
WhyAn empty cell in a number column looks like it holds nothing, and nothing feels like zero.
InsteadA blank satisfies no numeric comparison at all. Compare a count with and without the numeric pair to see how many rows are missing data.
Using > where >= was meant.
WhyAt least twelve and more than twelve are the same phrase in conversation.
InsteadDecide what happens to a row sitting exactly on the threshold, then check that row. This is the cause of nearly every count that is out by one.
Ranges of different heights.
WhyEach range is dragged separately.
InsteadEvery criteria range must match. Excel gives #VALUE! for a mismatch, which is at least honest; other spreadsheets return a number.
Common questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one criterion; COUNTIFS takes any number of pairs that must all hold on the same row. Both use the same argument order, so unlike SUMIF and SUMIFS there is no trap in switching between them.
Can COUNTIFS use OR logic?
Not inside one formula. Add separate COUNTIFS together, or build a helper column that flags the rows you want and count that.
How do I use COUNTIFS with a date range?
Two pairs against the same date column: =COUNTIFS(E2:E7, ">="&DATE(2024,1,1), E2:E7, "<="&DATE(2024,6,30)). Build each boundary with & so the date is a real date rather than text.
Why does COUNTIFS return 0?
No row satisfied every condition. Remove pairs one at a time until the count changes; the pair you removed last is the one excluding everything. Blanks and numbers stored as text are the usual reasons.