Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Two-way grids and mixed references. In a grid, the row label sits in one column and the column label in one row. $G2 locks the column but lets the row move, so every row reads its own label in G. H$1 locks the row but lets the column move, so every column reads its own header. Data ranges get $ on both.
=COUNTIFS($B$2:$B$80,$F2,$C$2:$C$80,G$1) tickets by team and priority
Grace Whitfield liked the region table and now wants the first two weeks of June split by sales channel as well: regions down the side, channels across the top.
In H2:J5, total the order values in E2:E44 for the region in column G and the channel in row 1. Write one formula in H2 that you can fill across and down. No rounding.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Region | Channel | Value | Region | Online | Sales Rep | Partner | Total | |
| 2 | 50460 | 2025-06-01 | East | Partner | 150.50 | North | 0.00 | ||||
| 3 | 50461 | 2025-06-02 | West | Sales Rep | 69.00 | South | 0.00 | ||||
| 4 | 50462 | 2025-06-02 | East | Online | 132.00 | East | 0.00 | ||||
| 5 | 50463 | 2025-06-02 | East | Online | 454.05 | West | 0.00 | ||||
| 6 | 50464 | 2025-06-02 | North | Online | 63.00 | Total | 0.00 | 0.00 | 0.00 | 0.00 | |
| 7 | 50465 | 2025-06-03 | North | Online | 92.20 | All orders | 14,016.90 | ||||
| 8 | 50466 | 2025-06-03 | East | Sales Rep | 115.43 | ||||||
| 9 | 50467 | 2025-06-03 | East | Online | 192.50 | ||||||
| 10 | 50468 | 2025-06-04 | South | Sales Rep | 439.90 | ||||||
| 11 | 50469 | 2025-06-04 | South | Sales Rep | 126.00 | ||||||
| 12 | 50470 | 2025-06-04 | North | Sales Rep | 190.75 | ||||||
| 13 | 50471 | 2025-06-05 | South | Online | 363.03 | ||||||
| 14 | 50472 | 2025-06-05 | East | Sales Rep | 31.00 | ||||||
| 15 | 50473 | 2025-06-05 | West | Partner | 622.50 | ||||||
| 16 | 50474 | 2025-06-05 | South | Online | 33.00 | ||||||
| 17 | 50475 | 2025-06-06 | West | Partner | 55.50 | ||||||
| 18 | 50476 | 2025-06-06 | West | Partner | 197.50 | ||||||
| 19 | 50477 | 2025-06-06 | West | Online | 99.00 | ||||||
| 20 | 50478 | 2025-06-06 | South | Online | 127.28 | ||||||
| 21 | 50479 | 2025-06-07 | East | Partner | 427.38 | ||||||
| 22 | 50480 | 2025-06-09 | North | Partner | 1,012.80 | ||||||
| 23 | 50481 | 2025-06-09 | West | Partner | 81.70 | ||||||
| 24 | 50482 | 2025-06-09 | East | Sales Rep | 436.67 | ||||||
| 25 | 50483 | 2025-06-09 | South | Online | 607.95 | ||||||
| 26 | 50484 | 2025-06-10 | East | Online | 18.05 | ||||||
| 27 | 50485 | 2025-06-10 | South | Partner | 37.20 | ||||||
| 28 | 50487 | 2025-06-10 | South | Online | 260.20 | ||||||
| 29 | 50488 | 2025-06-11 | East | Sales Rep | 129.00 | ||||||
| 30 | 50489 | 2025-06-11 | South | Sales Rep | 393.00 | ||||||
| 31 | 50490 | 2025-06-11 | North | Sales Rep | 194.83 | ||||||
| 32 | 50491 | 2025-06-11 | North | Online | 60.00 | ||||||
| 33 | 50492 | 2025-06-12 | West | Sales Rep | 259.75 | ||||||
| 34 | 50493 | 2025-06-12 | South | Sales Rep | 64.00 | ||||||
| 35 | 50494 | 2025-06-12 | West | Online | 471.90 | ||||||
| 36 | 50495 | 2025-06-12 | South | Sales Rep | 158.60 | ||||||
| 37 | 50496 | 2025-06-12 | East | Online | 64.50 | ||||||
| 38 | 50497 | 2025-06-13 | North | Online | 312.00 | ||||||
| 39 | 50498 | 2025-06-13 | West | Online | 294.60 | ||||||
| 40 | 50499 | 2025-06-13 | West | Partner | 308.23 | ||||||
| 41 | 50500 | 2025-06-13 | North | Sales Rep | 1,410.00 | ||||||
| 42 | 50501 | 2025-06-14 | South | Sales Rep | 44.00 | ||||||
| 43 | 51123 | 2025-06-02 | West | Sales Rep | 1,672.65 | ||||||
| 44 | 51124 | 2025-06-09 | West | Sales Rep | 1,743.75 | ||||||
| 45 | |||||||||||
| 46 | |||||||||||
| 47 |
=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