COUNTIF With Multiple Criteria: COUNTIFS Explained
· 7 min read
COUNTIF only takes one condition. For COUNTIF multiple criteria, use COUNTIFS: give it one "range, criteria" pair per condition, and it counts the rows where all of them are true.
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)For example, completed orders in the West region:
=COUNTIFS(B2:B9, "West", C2:C9, "completed")COUNTIFS works in every version of Excel since 2007, and in Google Sheets with the same syntax.
The example table
Eight orders. Column E is a customer rating, left empty when the customer did not reply. B11 and B12 hold two inputs that some formulas point at.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Amount | Rating | Question | Count | |
| 2 | 2001 | West | completed | 640 | 4 | West | 4 | |
| 3 | 2002 | East | completed | 1,150 | West, completed | 3 | ||
| 4 | 2003 | West | refunded | 220 | 2 | Region in B12, completed | 3 | |
| 5 | 2004 | North | completed | 980 | 5 | Over 1,000 | 2 | |
| 6 | 2005 | West | completed | 1,430 | Over the amount in B11 | 5 | ||
| 7 | 2006 | South | cancelled | 310 | West, 500 or more | 2 | ||
| 8 | 2007 | West | completed | 75 | 3 | 500 up to 1,000 | 3 | |
| 9 | 2008 | East | completed | 520 | 4 | Has a rating | 5 | |
| 10 | Has a rating (COUNTA) | 5 | ||||||
| 11 | Amount over | 500 | No rating | 3 | ||||
| 12 | Region | West | West, has a rating | 3 | ||||
| 13 | Refunded or cancelled | 2 |
COUNTIF with one condition
=COUNTIF(B2:B9, "West")Result: 4. Text criteria go in quotes and are not case sensitive, so "west" gives the same answer.
COUNTIFS with two or more conditions
=COUNTIFS(B2:B9, "West", C2:C9, "completed")Result: 3 (orders 2001, 2005 and 2007). Order 2003 is in the West but refunded, so it is not counted.
Each extra condition is another pair. All ranges must be the same size, or COUNTIFS returns #VALUE!.
Criteria from a cell
If B12 holds a region name (it holds West in the sheet), point at it instead of typing the name:
=COUNTIFS($B$2:$B$9, B12, $C$2:$C$9, "completed")Lock the data ranges with $ so they stay put when you fill the formula down a summary table.
COUNTIF greater than
Put the operator inside the quotes, in front of the number:
=COUNTIF(D2:D9, ">1000")Result: 2 (1,150 and 1,430). The operators are >, <, >=, <=, = and <>.
To compare with a value in a cell, keep the operator in quotes and join the cell with &. In the sheet, B11 holds 500:
=COUNTIF(D2:D9, ">"&B11)Result: 5. The orders of 220, 310 and 75 are not counted.
Greater than plus another condition
West orders of 500 or more:
=COUNTIFS(B2:B9, "West", D2:D9, ">=500")Result: 2 (640 and 1,430).
Between two numbers
Use the same column twice, once for each end of the range. Orders from 500 up to, but not including, 1,000:
=COUNTIFS(D2:D9, ">=500", D2:D9, "<1000")Result: 3 (640, 980 and 520). Decide whether each end is "or equal" and say so in your report. Off-by-one boundaries are a common reason two analysts get different counts.
COUNTIF not blank
To count cells that contain anything, use "<>" on its own:
=COUNTIF(E2:E9, "<>")Result: 5 ratings. COUNTA(E2:E9) also returns 5. To count the empty ones:
=COUNTBLANK(E2:E9)Result: 3. To count responders in one region, combine it with COUNTIFS: =COUNTIFS(B2:B9, "West", E2:E9, "<>") returns 3 (2001, 2003 and 2007).
A cell that holds a single space is not blank. If your count is higher than you expect, look for cells that only look empty.
COUNTIF OR: counting one value or another
COUNTIFS joins conditions with AND. For OR on the same column, add two COUNTIFs, or give one COUNTIF a list in curly braces and wrap it in SUM:
=COUNTIF(C2:C9, "refunded") + COUNTIF(C2:C9, "cancelled")
=SUM(COUNTIF(C2:C9, {"refunded","cancelled"}))Both return 2. A single row cannot be both "refunded" and "cancelled", so nothing is counted twice.
OR across different columns
This is where it gets tricky. Say you want orders that are West or over 1,000. Adding the counts gives 4 + 2 = 6, but order 2005 is West and over 1,000, so it was counted twice. Subtract the overlap:
=COUNTIF(B2:B9, "West") + COUNTIF(D2:D9, ">1000") - COUNTIFS(B2:B9, "West", D2:D9, ">1000")Result: 5. Here is the AI-style version next to the fixed one:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Amount | Rating | West or over 1,000 | Count | |
| 2 | 2001 | West | completed | 640 | 4 | AI's formula | 6 | |
| 3 | 2002 | East | completed | 1,150 | Overlap (both) | 1 | ||
| 4 | 2003 | West | refunded | 220 | 2 | Fixed | 5 | |
| 5 | 2004 | North | completed | 980 | 5 | |||
| 6 | 2005 | West | completed | 1,430 | ||||
| 7 | 2006 | South | cancelled | 310 | ||||
| 8 | 2007 | West | completed | 75 | 3 | |||
| 9 | 2008 | East | completed | 520 | 4 |
Another way that scales to more conditions is SUMPRODUCT:
=SUMPRODUCT(--(((B2:B9="West") + (D2:D9>1000)) > 0))Each comparison returns TRUE or FALSE per row. Adding them gives 0, 1 or 2. > 0 turns that into "at least one is true", and -- converts TRUE and FALSE into 1 and 0 so SUMPRODUCT can add them. Result: 5.
Common mistakes
Adding OR counts across two columns. When you ask an AI assistant to "count orders that are West or over 1,000", it will often give you two COUNTIFs added together. The formula has no error and the number looks plausible, but rows that meet both conditions are counted twice. A quick check: run a COUNTIFS for the overlap (rows that meet both conditions). If it is more than 0, the simple sum is too high by exactly that number.
Counting every status. "How many West orders did we have?" and "How many completed West orders did we have?" are different questions. AI answers often leave the status condition out. Read every criteria pair and ask whether refunded and cancelled rows should be in the count.
Quoting the cell reference. =COUNTIF(D2:D9, ">B11") compares the amounts with the text "B11", not with the number in B11, so the count is wrong (usually 0). The operator goes in quotes, the cell does not: ">"&B11.
Ranges of different sizes. =COUNTIFS(B2:B9, "West", C2:C10, "completed") returns #VALUE!. That one is easy to see. A range that is the same size but starts one row lower gives no error and a wrong count, which is much harder to spot.
Hidden spaces and numbers stored as text. "West " with a trailing space does not match "West". Amounts stored as text are ignored by ">1000". If a count looks low, test one row with =B3="West".
FAQ
How do I use COUNTIF with multiple criteria?
Switch to COUNTIFS and add one "range, criteria" pair per condition: =COUNTIFS(B2:B9, "West", C2:C9, "completed"). It counts rows where every condition is true.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs. COUNTIFS with a single pair gives the same answer as COUNTIF.
How do I count cells that are not blank?
Use =COUNTIF(range, "<>") or =COUNTA(range). Use COUNTBLANK for the empty ones.
Can COUNTIF do "or"?
Not on its own. Add two COUNTIFs for values in the same column, or use SUM(COUNTIF(range, {"a","b"})). For OR across different columns, subtract the overlap or use SUMPRODUCT.
How do I add the matching values instead of counting them?
Use SUMIFS, which takes the same criteria pairs plus a column to add. Our guide to SUMIFS with multiple criteria walks through it. COUNTIFS questions also come up a lot in Excel interview questions for data analysts.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Count the completed orders” →