Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Conditions inside SUMPRODUCT. A comparison such as (B2:B47=I1) gives TRUE or FALSE for every row. Multiplying two of them gives 1 only where both are true, and 0 elsewhere, so (range=x)*(range=y) works as AND. Put it in front of the values and only the matching rows are added.
=SUMPRODUCT((A2:A60="Paid")*(B2:B60="EU"),C2:C60) paid EU invoices
Marcus Hill wants a quick check cell: revenue for the region in I1 and the category in I2 from the first week of September’s order lines. The lines have no revenue column, and he doesn’t want one added.
In I3, calculate revenue (quantity in D × unit price in E × (1 − discount in F)) for only the lines in rows 2 to 47 whose region in B matches I1 and whose category in C matches I2, in one formula. Round to 2 decimals.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Category | Qty | Unit price | Discount | Region | South | |
| 2 | 50733 | East | Paper & Print | 6 | 9.90 | 0% | Category | Coffee & Kitchen | |
| 3 | 50734 | West | Desk Supplies | 5 | 15.50 | 0% | Revenue | ||
| 4 | 50734 | West | Coffee & Kitchen | 4 | 24.00 | 0% | |||
| 5 | 50735 | North | Coffee & Kitchen | 13 | 36.00 | 0% | |||
| 6 | 50736 | South | Paper & Print | 2 | 84.00 | 0% | |||
| 7 | 50736 | South | Cleaning | 1 | 25.00 | 5% | |||
| 8 | 50736 | South | Desk Supplies | 5 | 13.50 | 5% | |||
| 9 | 50737 | North | Coffee & Kitchen | 12 | 24.00 | 0% | |||
| 10 | 50737 | North | Cleaning | 12 | 23.50 | 0% | |||
| 11 | 50738 | South | Coffee & Kitchen | 30 | 21.50 | 5% | |||
| 12 | 50739 | South | Coffee & Kitchen | 4 | 36.00 | 5% | |||
| 13 | 50739 | South | Coffee & Kitchen | 3 | 21.50 | 0% | |||
| 14 | 50739 | South | Desk Supplies | 2 | 9.50 | 0% | |||
| 15 | 50739 | South | Cleaning | 4 | 15.00 | 0% | |||
| 16 | 50740 | West | Paper & Print | 13 | 6.20 | 5% | |||
| 17 | 50740 | West | Cleaning | 14 | 15.00 | 0% | |||
| 18 | 50740 | West | Coffee & Kitchen | 7 | 36.00 | 10% | |||
| 19 | 50741 | South | Coffee & Kitchen | 13 | 16.50 | 5% | |||
| 20 | 50742 | North | Coffee & Kitchen | 14 | 11.00 | 0% | |||
| 21 | 50742 | North | Cleaning | 3 | 15.00 | 0% | |||
| 22 | 50743 | North | Paper & Print | 13 | 84.00 | 0% | |||
| 23 | 50743 | North | Desk Supplies | 9 | 22.00 | 5% | |||
| 24 | 50743 | North | Paper & Print | 8 | 5.50 | 10% | |||
| 25 | 50744 | North | Coffee & Kitchen | 6 | 11.00 | 0% | |||
| 26 | 50745 | North | Paper & Print | 3 | 12.50 | 10% | |||
| 27 | 50746 | South | Desk Supplies | 18 | 19.00 | 0% | |||
| 28 | 50746 | South | Paper & Print | 11 | 9.90 | 0% | |||
| 29 | 50746 | South | Cleaning | 21 | 17.50 | 0% | |||
| 30 | 50746 | South | Desk Supplies | 15 | 9.50 | 10% | |||
| 31 | 50747 | South | Paper & Print | 6 | 6.20 | 5% | |||
| 32 | 50747 | South | Desk Supplies | 5 | 22.00 | 0% | |||
| 33 | 50748 | East | Cleaning | 5 | 14.00 | 5% | |||
| 34 | 50748 | East | Coffee & Kitchen | 12 | 21.50 | 5% | |||
| 35 | 50748 | East | Cleaning | 12 | 23.50 | 0% | |||
| 36 | 50748 | East | Paper & Print | 5 | 9.90 | 0% | |||
| 37 | 50749 | East | Coffee & Kitchen | 8 | 11.00 | 0% | |||
| 38 | 50749 | East | Coffee & Kitchen | 11 | 16.50 | 0% | |||
| 39 | 50749 | East | Cleaning | 5 | 23.50 | 0% | |||
| 40 | 50749 | East | Paper & Print | 8 | 59.00 | 10% | |||
| 41 | 50750 | North | Desk Supplies | 5 | 15.50 | 0% | |||
| 42 | 50750 | North | Coffee & Kitchen | 3 | 16.50 | 0% | |||
| 43 | 50751 | North | Cleaning | 6 | 25.00 | 0% | |||
| 44 | 50751 | North | Desk Supplies | 7 | 19.00 | 5% | |||
| 45 | 50751 | North | Coffee & Kitchen | 7 | 16.50 | 5% | |||
| 46 | 50751 | North | Desk Supplies | 11 | 22.00 | 0% | |||
| 47 | 50752 | South | Desk Supplies | 5 | 22.00 | 0% | |||
| 48 | |||||||||
| 49 | |||||||||
| 50 |
=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