Array Functions
Advanced
Capstone: build a live regional summary
Everything in this track, in one report.
Task:
Finance wants a summary that maintains itself. Three cells, three formulas. In E2, list each region once from B2:B9. In F2, list the same regions ordered by name. In G2, list the order numbers from A2:A9 for orders over 1000.
Learning Objectives:
- Combine UNIQUE, SORT and FILTER
- Chain formulas with a spilled reference
- Leave room for results to spill
Hints
Solve without hints for +5 XP
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Regions | Sorted | Large orders | |
| 2 | SO-1041 | North | 1200 | ||||
| 3 | SO-1042 | South | 890 | ||||
| 4 | SO-1043 | North | 2100 | ||||
| 5 | SO-1044 | East | 640 | ||||
| 6 | SO-1045 | South | 1750 | ||||
| 7 | SO-1046 | North | 430 | ||||
| 8 | SO-1047 | West | 980 | ||||
| 9 | SO-1048 | East | 1310 |
What this exercise teaches (contains the answer)
Three formulas and the report maintains itself. Add an order next month and every one of them resizes. F2 pointing at E2# rather than E2:E5 is the detail that makes it durable — hardcode the range and it breaks the first time a new region appears.