About this lesson
Criteria in cells: tests you can change without editing formulas
Point the criteria at input cells instead of typing them into the formula: =COUNTIF(B6:B17,B1) matches whatever is in B1, and =COUNTIF(D6:D17,">="&B2) joins an operator to the cell with &. Writing ">=B2" inside the quotes compares against the text B2 and counts nothing.
=COUNTIF(B6:B17,B1)What you'll practise
Build a gym chain's club review where the state, membership threshold and cut-off date are inputs, so one change updates every answer.
4 steps · 40 points · the cells outlined in gold below
- COUNTIF
- SUMIFS
- criteria in cells
- & operator
- inputs
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | State | VIC | |||
| 2 | Min members | 2,000 | |||
| 3 | Opened before | 01/07/2023 | |||
| 4 | |||||
| 5 | Club | State | Opened | Members | EBITDA ($k) |
| 6 | Fitzroy | VIC | 04/03/2019 | 2,640 | 812 |
| 7 | Geelong Waterfront | VIC | 16/08/2021 | 1,870 | 455 |
| 8 | Box Hill | VIC | 11/09/2023 | 1,520 | 128 |
| 9 | Ballarat | VIC | 23/05/2022 | 2,215 | 604 |
| 10 | Toowoomba | QLD | 05/02/2024 | 1,190 | -46 |
| 11 | Surry Hills | NSW | 12/11/2018 | 3,105 | 1,037 |
| 12 | Parramatta | NSW | 29/06/2020 | 2,780 | 869 |
| 13 | Newcastle | NSW | 03/10/2022 | 2,390 | 517 |
| 14 | Penrith | NSW | 03/07/2023 | 1,645 | 92 |
| 15 | Chatswood | NSW | 15/04/2024 | 2,510 | 233 |
| 16 | New Farm | QLD | 30/09/2019 | 2,455 | 721 |
| 17 | Southport | QLD | 20/03/2023 | 1,980 | 386 |
| 18 | |||||
| 19 | Clubs in the state | ||||
| 20 | Clubs meeting threshold | ||||
| 21 | Mature clubs' EBITDA ($k) |