Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
A region × month grid. Combine the two patterns: a text criterion for the row label, and a pair of date criteria for the month in the column header. Each label gets one $: before the column for labels down the side, before the row for headers across the top.
=SUMIFS($E$2:$E$99,$B$2:$B$99,$G2,$A$2:$A$99,">="&H$1,$A$2:$A$99,"<="&EOMONTH(H$1,0))
The partner programme manager wants Q1 partner revenue with regions down the side and months across the top, built so next quarter only needs new dates in row 1.
In G2:I5, total the order values in D2:D40 for the region in column F and the month that starts on the date in row 1 (first to last day, both included). Write one formula in G2 that fills across and down. No rounding.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Region | Value | Region | 2025-01-01 | 2025-02-01 | 2025-03-01 | Q1 | |
| 2 | 50004 | 2025-01-01 | North | 198.35 | North | 0.00 | ||||
| 3 | 50020 | 2025-01-07 | South | 47.50 | South | 0.00 | ||||
| 4 | 50021 | 2025-01-07 | West | 397.63 | East | 0.00 | ||||
| 5 | 50028 | 2025-01-09 | North | 226.13 | West | 0.00 | ||||
| 6 | 50030 | 2025-01-10 | East | 1,589.00 | Total | 0.00 | 0.00 | 0.00 | 0.00 | |
| 7 | 50031 | 2025-01-10 | South | 218.00 | All orders | 14,524.49 | ||||
| 8 | 50034 | 2025-01-13 | North | 99.50 | ||||||
| 9 | 50058 | 2025-01-21 | South | 410.40 | ||||||
| 10 | 50062 | 2025-01-22 | North | 323.10 | ||||||
| 11 | 50069 | 2025-01-24 | East | 54.15 | ||||||
| 12 | 50081 | 2025-01-28 | East | 237.70 | ||||||
| 13 | 50083 | 2025-01-29 | South | 280.80 | ||||||
| 14 | 50084 | 2025-01-29 | North | 89.70 | ||||||
| 15 | 50085 | 2025-01-29 | West | 156.50 | ||||||
| 16 | 50096 | 2025-02-03 | North | 1,535.20 | ||||||
| 17 | 50103 | 2025-02-04 | North | 663.50 | ||||||
| 18 | 50111 | 2025-02-06 | North | 274.00 | ||||||
| 19 | 50113 | 2025-02-07 | West | 165.00 | ||||||
| 20 | 50123 | 2025-02-11 | West | 255.70 | ||||||
| 21 | 50131 | 2025-02-13 | South | 144.00 | ||||||
| 22 | 50136 | 2025-02-14 | South | 677.75 | ||||||
| 23 | 50139 | 2025-02-17 | North | 1,315.41 | ||||||
| 24 | 50144 | 2025-02-18 | East | 181.50 | ||||||
| 25 | 50156 | 2025-02-21 | East | 71.00 | ||||||
| 26 | 50157 | 2025-02-21 | West | 126.70 | ||||||
| 27 | 50164 | 2025-02-24 | East | 315.00 | ||||||
| 28 | 50171 | 2025-02-26 | South | 237.18 | ||||||
| 29 | 50173 | 2025-02-27 | South | 123.00 | ||||||
| 30 | 50177 | 2025-02-28 | South | 1,381.00 | ||||||
| 31 | 50181 | 2025-03-01 | West | 123.90 | ||||||
| 32 | 50183 | 2025-03-03 | East | 102.13 | ||||||
| 33 | 50202 | 2025-03-10 | South | 644.81 | ||||||
| 34 | 50204 | 2025-03-10 | East | 258.10 | ||||||
| 35 | 50213 | 2025-03-12 | West | 151.45 | ||||||
| 36 | 50223 | 2025-03-15 | East | 446.00 | ||||||
| 37 | 50242 | 2025-03-21 | West | 327.00 | ||||||
| 38 | 50255 | 2025-03-26 | South | 323.00 | ||||||
| 39 | 50260 | 2025-03-28 | South | 337.20 | ||||||
| 40 | 50261 | 2025-03-28 | East | 16.50 | ||||||
| 41 | ||||||||||
| 42 | ||||||||||
| 43 |
=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