Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Spill references (E2#). E2# means the whole spill that starts in E2, however many rows it has today. Give it to SUMIFS or COUNTIFS as the criteria and they return one result per label, spilling alongside it. When the label list grows or shrinks, the results follow.
=COUNTIFS(C2:C40,E2#)
Grace Whitfield's December dashboard already lists the regions with one spilling formula in I2. She wants the order count beside it, and it has to grow with the list if a region is added.
In J2, write one formula that returns the number of orders (rows of A2:G32) for every region in the I2 spill. Point at the whole spill with I2#, not at a fixed range.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | Order date | Customer | Region | Channel | Status | Value | Region | Orders | |
| 2 | 51006 | 2025-12-01 | Pioneer Consulting | North | Sales Rep | completed | $371.80 | East | ||
| 3 | 51007 | 2025-12-01 | Willow Studios | North | Sales Rep | completed | $157.50 | North | ||
| 4 | 51008 | 2025-12-01 | Harbor Labs | East | Online | completed | $320.45 | South | ||
| 5 | 51009 | 2025-12-01 | Falcon Consulting | North | Sales Rep | completed | $42.00 | West | ||
| 6 | 51010 | 2025-12-02 | Beacon Hotels | East | Partner | completed | $275.60 | |||
| 7 | 51011 | 2025-12-02 | Willow Law Group | East | Online | completed | $224.00 | |||
| 8 | 51012 | 2025-12-02 | Copper Media | South | Online | completed | $251.55 | |||
| 9 | 51013 | 2025-12-03 | Willow Studios | North | Sales Rep | completed | $29.70 | |||
| 10 | 51014 | 2025-12-03 | River Print Co | West | Online | completed | $182.50 | |||
| 11 | 51015 | 2025-12-03 | Maple Academy | East | Sales Rep | completed | $841.60 | |||
| 12 | 51016 | 2025-12-03 | Willow Academy | North | Online | completed | $181.00 | |||
| 13 | 51017 | 2025-12-04 | Orchard Media | West | Online | completed | $74.00 | |||
| 14 | 51018 | 2025-12-04 | Willow Hotels | West | Sales Rep | completed | $86.40 | |||
| 15 | 51019 | 2025-12-04 | Copper Media | South | Online | completed | $148.50 | |||
| 16 | 51020 | 2025-12-04 | Bright Hotels | East | Sales Rep | completed | $118.50 | |||
| 17 | 51021 | 2025-12-05 | Harbor Fitness | South | Sales Rep | completed | $152.40 | |||
| 18 | 51022 | 2025-12-05 | Copper Media | South | Online | completed | $175.00 | |||
| 19 | 51023 | 2025-12-05 | Orchard Clinic | South | Online | cancelled | $823.00 | |||
| 20 | 51024 | 2025-12-05 | Copper Fitness | South | Sales Rep | completed | $223.00 | |||
| 21 | 51025 | 2025-12-06 | Pioneer Realty | West | Sales Rep | completed | $195.70 | |||
| 22 | 51026 | 2025-12-08 | Blue Accounting | North | Online | completed | $92.40 | |||
| 23 | 51027 | 2025-12-08 | Willow Hotels | West | Sales Rep | completed | $248.47 | |||
| 24 | 51028 | 2025-12-08 | Beacon Architects | North | Online | completed | $219.60 | |||
| 25 | 51029 | 2025-12-09 | Orchard Academy | South | Online | completed | $402.55 | |||
| 26 | 51030 | 2025-12-09 | Copper Media | South | Online | completed | $169.50 | |||
| 27 | 51031 | 2025-12-09 | Harbor Consulting | North | Online | completed | $747.83 | |||
| 28 | 51032 | 2025-12-10 | North Architects | South | Partner | completed | $19.80 | |||
| 29 | 51033 | 2025-12-10 | Cedar Realty | North | Sales Rep | completed | $122.50 | |||
| 30 | 51034 | 2025-12-10 | Harbor Hotels | West | Partner | completed | $182.00 | |||
| 31 | 51035 | 2025-12-10 | Harbor Studios | East | Online | refunded | $504.00 | |||
| 32 | 51036 | 2025-12-10 | Pioneer Realty | West | Online | completed | $105.00 | |||
| 33 | ||||||||||
| 34 | ||||||||||
| 35 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe