COUNTIFS with the conditions in cells
PremiumIn F3, count the reps from the region in F1 with sales of at least the amount in F2. Point at both cells: F1 as it is, and F2 joined to ">=".
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | Region | North | ||
| 2 | North | Ann | 420 | At least | 300 | ||
| 3 | South | Ben | 310 | Reps | |||
| 4 | North | Cy | 150 | ||||
| 5 | West | Di | 500 | ||||
| 6 | South | Ed | 260 | ||||
| 7 | North | Fay | 380 | ||||
| 8 | North | Gus | 290 | ||||
| 9 | West | Hal | 340 | ||||
| 10 |
Premium lesson
Unlock COUNTIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
A criterion that is just "equal to" can be the cell itself, F1. A criterion with an operator joins the operator and the cell, ">="&F2, which becomes ">=300". Ann (420) and Fay (380) are North reps with at least 300. Change F1 to West and the answer becomes 2 again, Di and Hal.
Common mistake: Typing North and 300 into the formula. It gives the right answer once, and the inputs in F1 and F2 stop doing anything.
The answer
- F3
=COUNTIFS(1A2:A9,2F1,3C2:C9,4">="&F2)
In plain words: Count the rows where 1the region in A2:A9 is 2the region in F1 and 3the sales in C2:C9 are 4at least the amount in F2.