COUNTIFS with two conditions
Step 1 of 3
COUNTIFS counts the rows where every condition holds. It takes pairs: a range, then the condition for that range.
Click Continue when you're ready.
Already know it? Type the formula straight into E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | North over 400 | ||
| 2 | North | Shoes | 500 | |||
| 3 | South | Shoes | 300 | |||
| 4 | North | Bags | 250 | |||
| 5 | North | Shoes | 420 | |||
| 6 | South | Bags | 180 | |||
| 7 | North | Bags | 150 | |||
| 8 | South | Shoes | 610 | |||
| 9 | North | Shoes | 90 | |||
| 10 |
Need help?
Why this works
COUNTIFS counts the rows where every condition holds. North has five orders, and two of them (500 and 420) are over 400. COUNTIFS has no sum range, so it starts with the first condition range.
Common mistake: Using ranges of different sizes, like A2:A9 and C2:C8. Every range must cover the same rows.
The answer
- E2
=COUNTIFS(1A2:A9,2"North",3C2:C9,4">400")
In plain words: Count the rows where 1the region in A2:A9 is 2North and 3the sales in C2:C9 are 4over 400.
+15 points