Criteria from a cell
1 / 5
How many people reached the target in F1? The answer goes in F2.
keyboard Typing formulas is easiest on a computer with a keyboard.
A1
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Sales | Target | 610 | |||
| 2 | Tariq | 720 | Reached it | ||||
| 3 | Nadia | 230 | |||||
| 4 | Daniel | 310 | |||||
| 5 | Quinn | 450 | |||||
| 6 | Jamal | 230 | |||||
| 7 | Isla | 750 | |||||
| 8 | Kofi | 610 | |||||
| 9 | Yusuf | 300 | |||||
| 10 | Sam | 950 | |||||
| 11 | Rosa | 370 | |||||
| 12 | |||||||
| 13 | |||||||
| 14 | |||||||
| 15 |
sales
target
how many?
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
How COUNTIF and SUMIFS works
Why take the condition from a cell?
=COUNTIF(B2:B11,">=500")works, but 500 is typed into the formula. Put the target in a cell instead, and the count updates whenever the target changes.How do you join the operator and the cell?
Keep the operator in quotes and join the cell with
&:=COUNTIF(B2:B11,">="&F1) Why not ">=
F1"?Inside quotes,
F1is just the letters F and 1, not a cell. Excel compares the sales with the text "F1" and counts nothing. The same goes forSUMIF,AVERAGEIFand theIFSversions.
Know someone fighting with a spreadsheet? Send them this page.