Criteria from a cell
Count the sales that reach a target typed in a cell.
Join the sign to the cell: ">="&F1, so changing F1 changes the count.
In F2, count how many people sold at least the target in F1. Use the cell F1 in your formula, not the number.
Typing formulas is easiest on a computer with a keyboard.
| 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 |
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.