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.
It comes up in this mock test: Excel test for job interviews
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.