Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
FILTER's if_empty. When no row matches, FILTER returns #CALC!. The third argument, if_empty, is what to show instead, for example "None" or 0. It only appears when the result would otherwise be empty, so the formula keeps working when matches come back.
=FILTER(A2:E40,C2:C40="Cleaning","No cleaning orders")
Finance sends each regional manager the year's refunds for one reason, chosen in I1. Elena Petrova's North sheet came back full of #CALC! errors last time, because North had no refunds for that reason.
In H4, list the rows of A2:F59 for the region in H3 and the reason in I1. In H10, do the same for the region in H9. When nothing matches, the formula must show the text None instead of an error. Point at H3, H9 and I1 rather than typing the names.
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Refund ID | Refund date | Customer | Region | Reason | Amount | Reason | Late delivery | |||||
| 2 | 1 | 2025-01-12 | Beacon Hotels | East | Late delivery | $165.25 | |||||||
| 3 | 2 | 2025-01-12 | Pioneer Motors | South | Changed mind | $10.45 | West | ||||||
| 4 | 3 | 2025-01-13 | Copper Pharmacy | South | Damaged item | $236.82 | |||||||
| 5 | 4 | 2025-01-17 | Cedar Accounting | West | Changed mind | $366.60 | |||||||
| 6 | 5 | 2025-01-19 | Atlas Bakery | South | Damaged item | $410.85 | |||||||
| 7 | 6 | 2025-01-23 | Harbor Consulting | North | Changed mind | $1,214.60 | |||||||
| 8 | 7 | 2025-01-24 | Lakeside Studios | East | Late delivery | $52.25 | |||||||
| 9 | 8 | 2025-01-30 | Atlas Dental | West | Changed mind | $420.00 | North | ||||||
| 10 | 9 | 2025-02-11 | Granite Clinic | East | Late delivery | $136.78 | |||||||
| 11 | 10 | 2025-02-18 | Orchard Realty | South | Wrong item | $582.10 | |||||||
| 12 | 11 | 2025-02-23 | River Print Co | West | Changed mind | $124.40 | |||||||
| 13 | 12 | 2025-03-19 | River Labs | South | Damaged item | $42.00 | |||||||
| 14 | 13 | 2025-03-30 | Atlas Consulting | South | $326.00 | ||||||||
| 15 | 14 | 2025-04-02 | North Bakery | South | Damaged item | $221.50 | |||||||
| 16 | 15 | 2025-04-02 | North Studios | North | Changed mind | $430.70 | |||||||
| 17 | 16 | 2025-04-10 | Lakeside Academy | West | Changed mind | $288.25 | |||||||
| 18 | 17 | 2025-04-16 | North Print Co | North | Changed mind | $84.00 | |||||||
| 19 | 18 | 2025-04-24 | Bright Motors | East | Damaged item | $27.10 | |||||||
| 20 | 19 | 2025-04-28 | Cedar Labs | East | Late delivery | $522.67 | |||||||
| 21 | 20 | 2025-04-30 | Meadow Consulting | East | Late delivery | $187.80 | |||||||
| 22 | 21 | 2025-05-08 | Atlas Realty | East | Damaged item | $216.72 | |||||||
| 23 | 22 | 2025-05-11 | Granite Bakery | West | Damaged item | $211.80 | |||||||
| 24 | 23 | 2025-05-13 | Blue Accounting | North | Damaged item | $242.00 | |||||||
| 25 | 24 | 2025-05-28 | Willow Law Group | East | Late delivery | $91.00 | |||||||
| 26 | 25 | 2025-05-30 | Maple Academy | East | Late delivery | $184.40 | |||||||
| 27 | 26 | 2025-06-14 | Harbor Bakery | East | Damaged item | $26.40 | |||||||
| 28 | 27 | 2025-06-29 | Beacon Architects | North | Damaged item | $271.80 | |||||||
| 29 | 28 | 2025-06-29 | Orchard Consulting | South | Wrong item | $343.70 | |||||||
| 30 | 29 | 2025-07-05 | River Print Co | West | Late delivery | $231.50 | |||||||
| 31 | 30 | 2025-07-19 | Orchard Labs | South | Damaged item | $330.00 | |||||||
| 32 | 31 | 2025-07-20 | Beacon Hotels | East | Late delivery | $223.80 | |||||||
| 33 | 32 | 2025-07-26 | Oak Fitness | North | Damaged item | $280.75 | |||||||
| 34 | 33 | 2025-07-31 | North Academy | West | Changed mind | $270.00 | |||||||
| 35 | 34 | 2025-08-05 | Oak Studios | West | Changed mind | $620.50 | |||||||
| 36 | 35 | 2025-08-08 | River Academy | West | Damaged item | $36.80 | |||||||
| 37 | 36 | 2025-08-10 | Oak Academy | East | Late delivery | $392.37 | |||||||
| 38 | 37 | 2025-08-19 | North Print Co | North | Wrong item | $74.10 | |||||||
| 39 | 38 | 2025-08-22 | Blue Accounting | North | Wrong item | $11.78 | |||||||
| 40 | 39 | 2025-08-23 | Silver Fitness | South | Changed mind | $62.00 | |||||||
| 41 | 40 | 2025-08-24 | Falcon Studios | West | Damaged item | $75.00 | |||||||
| 42 | 41 | 2025-09-03 | Orchard Logistics | West | Late delivery | $264.32 | |||||||
| 43 | 42 | 2025-09-03 | Cedar Labs | East | Late delivery | $50.00 | |||||||
| 44 | 43 | 2025-09-15 | North Architects | South | Wrong item | $1,011.60 | |||||||
| 45 | 44 | 2025-09-19 | Maple Academy | East | Late delivery | $317.15 | |||||||
| 46 | 45 | 2025-10-04 | Pioneer Realty | West | $265.10 | ||||||||
| 47 | 46 | 2025-10-16 | North Architects | South | Wrong item | $59.40 | |||||||
| 48 | 47 | 2025-10-17 | Orchard Media | West | Damaged item | $330.25 | |||||||
| 49 | 48 | 2025-11-07 | Willow Hotels | West | Damaged item | $125.00 | |||||||
| 50 | 49 | 2025-11-14 | Cedar Accounting | West | Wrong item | $59.70 | |||||||
| 51 | 50 | 2025-11-15 | Granite Labs | South | Damaged item | $144.50 | |||||||
| 52 | 51 | 2025-11-23 | Bright Labs | South | Damaged item | $94.00 | |||||||
| 53 | 52 | 2025-11-24 | Oak Bakery | East | Damaged item | $359.25 | |||||||
| 54 | 53 | 2025-11-25 | Orchard Logistics | West | Late delivery | $155.00 | |||||||
| 55 | 54 | 2025-12-01 | Summit Realty | South | Wrong item | $524.00 | |||||||
| 56 | 55 | 2025-12-24 | Harbor Studios | East | Changed mind | $504.00 | |||||||
| 57 | 56 | 2025-12-26 | River Labs | South | Late delivery | $77.95 | |||||||
| 58 | 57 | 2025-12-30 | Willow Labs | West | Damaged item | $410.19 | |||||||
| 59 | 58 | 2025-12-31 | Harbor Bakery | East | Lost in transit | $125.37 | |||||||
| 60 | |||||||||||||
| 61 | |||||||||||||
| 62 |
=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