Practice by topic / Excel / Conditional Aggregation
SUMIF, SUMIFS and SUMPRODUCT total only the rows that match: sales for one region, costs for one month, hours for one person. They're among the most-used formulas at work. These 10 exercises build up step by step, starting with sum sales from a region above a value, sum revenue for a product above a quantity, sum hours for a project in a status.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A4 hold regions, B1:B4 hold amounts, C1:C4 hold sale totals to sum. Write a formula in D1 that sums C where region is West and amount is over 100.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | West | 150 | 10 | ? |
| 2 | West | 50 | 20 | |
| 3 | East | 200 | 30 | |
| 4 | West | 300 | 15 |
Use =SUMIFS(C1:C4,A1:A4,"West",B1:B4,">100")
Right — SUMIFS requires all criteria pairs to match on the same row before including that row's value.
Expected result in D1: 25
A1:A4 hold products, B1:B4 hold quantities, C1:C4 hold revenue. Write a formula in D1 that sums revenue for 'Widget' orders with quantity over 5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Widget | 10 | 100 | ? |
| 2 | Widget | 3 | 30 | |
| 3 | Gadget | 8 | 80 | |
| 4 | Widget | 7 | 70 |
Use =SUMIFS(C1:C4,A1:A4,"Widget",B1:B4,">5")
Good — SUMIFS filters on both the product and quantity conditions together.
Expected result in D1: 170
A1:A4 hold projects, B1:B4 hold statuses, C1:C4 hold hours. Write a formula in D1 that sums hours for 'Apollo' tasks that are 'active'.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Apollo | active | 5 | ? |
| 2 | Apollo | done | 3 | |
| 3 | Titan | active | 4 | |
| 4 | Apollo | active | 6 |
Use =SUMIFS(C1:C4,A1:A4,"Apollo",B1:B4,"active")
Right — both the project name and status must match for the row to count.
Expected result in D1: 11
A1:A4 hold categories, B1:B4 hold sequence numbers, C1:C4 hold spend. Write a formula in D1 that sums 'Travel' spend with sequence number under 100.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Travel | 50 | 200 | ? |
| 2 | Travel | 150 | 300 | |
| 3 | Food | 20 | 40 | |
| 4 | Travel | 80 | 120 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold rep names, B1:B4 hold sales counts, C1:C4 hold commission amounts. Write a formula in D1 that sums commission for 'Sam' with sales counts of 10 or more.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sam | 12 | 100 | ? |
| 2 | Sam | 5 | 40 | |
| 3 | Lee | 15 | 120 | |
| 4 | Sam | 20 | 200 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold carriers, B1:B4 hold days late, C1:C4 hold penalty amounts. Write a formula in D1 that sums penalties for 'FastShip' shipments that were more than 2 days late.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | FastShip | 3 | 20 | ? |
| 2 | FastShip | 1 | 5 | |
| 3 | SlowCo | 5 | 30 | |
| 4 | FastShip | 4 | 25 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold departments, B1:B4 hold ratings, C1:C4 hold bonus amounts. Write a formula in D1 that sums bonuses in 'Sales' with a rating above 3.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sales | 4 | 500 | ? |
| 2 | Sales | 2 | 100 | |
| 3 | Support | 5 | 400 | |
| 4 | Sales | 5 | 600 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold reasons, B1:B4 hold amounts, C1:C4 hold refund totals. Write a formula in D1 that sums refunds for 'defective' items with amount under 100.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | defective | 50 | 50 | ? |
| 2 | defective | 150 | 150 | |
| 3 | wrong size | 40 | 40 | |
| 4 | defective | 80 | 80 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold class levels, B1:B4 hold attendee counts, C1:C4 hold fees collected. Write a formula in D1 that sums fees for 'Beginner' classes with 10+ attendees.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Beginner | 12 | 240 | ? |
| 2 | Beginner | 5 | 100 | |
| 3 | Advanced | 15 | 450 | |
| 4 | Beginner | 20 | 400 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold locations, B1:B4 hold usage units, C1:C4 hold costs. Write a formula in D1 that sums costs for 'Warehouse' with usage over 500.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Warehouse | 600 | 300 | ? |
| 2 | Warehouse | 400 | 200 | |
| 3 | Office | 700 | 350 | |
| 4 | Warehouse | 800 | 400 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →