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 for one region, sum expenses over a threshold, sum orders from a specific customer.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A5 hold regions, B1:B5 hold sales. Write a formula in C1 that sums sales for West only.
| A | B | C | |
|---|---|---|---|
| 1 | West | 120 | ? |
| 2 | East | 80 | |
| 3 | West | 200 | |
| 4 | North | 90 | |
| 5 | West | 60 |
Use =SUMIF(A1:A5,"West",B1:B5)
Right — SUMIF sums only the rows where the condition matches.
Expected result in C1: 380
A1:A5 hold expense amounts. Write a formula in A6 that sums only the expenses over 100.
| A | |
|---|---|
| 1 | 50 |
| 2 | 150 |
| 3 | 80 |
| 4 | 200 |
| 5 | 40 |
| 6 | ? |
Use =SUMIF(A1:A5,">100")
Good — when the criteria range and sum range are the same, the third argument can be omitted.
Expected result in A6: 350
A1:A5 hold customer names, B1:B5 hold order totals. Write a formula in C1 that sums all orders from 'Acme'.
| A | B | C | |
|---|---|---|---|
| 1 | Acme | 300 | ? |
| 2 | Globex | 150 | |
| 3 | Acme | 220 | |
| 4 | Initech | 90 | |
| 5 | Acme | 180 |
Use =SUMIF(A1:A5,"Acme",B1:B5)
Right — SUMIF filters to the matching customer before summing.
Expected result in C1: 700
A1:A5 hold adjustment amounts, some negative. Write a formula in A6 that sums only the positive ones.
| A | |
|---|---|
| 1 | 20 |
| 2 | -15 |
| 3 | 30 |
| 4 | -5 |
| 5 | 10 |
| 6 | ? |
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:A5 hold project names, B1:B5 hold hours logged. Write a formula in C1 that sums hours for 'Apollo' only.
| A | B | C | |
|---|---|---|---|
| 1 | Apollo | 8 | ? |
| 2 | Titan | 5 | |
| 3 | Apollo | 6 | |
| 4 | Apollo | 4 | |
| 5 | Titan | 7 |
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:A5 hold donation amounts. Write a formula in A6 that sums only donations under 50 (to see small-donor total).
| A | |
|---|---|
| 1 | 20 |
| 2 | 100 |
| 3 | 35 |
| 4 | 75 |
| 5 | 45 |
| 6 | ? |
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:A5 hold reason codes, B1:B5 hold refund amounts. Write a formula in C1 that sums refunds coded 'defective'.
| A | B | C | |
|---|---|---|---|
| 1 | defective | 40 | ? |
| 2 | changed mind | 25 | |
| 3 | defective | 60 | |
| 4 | wrong size | 30 | |
| 5 | defective | 20 |
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:A5 hold game locations ('Home'/'Away'), B1:B5 hold points scored. Write a formula in C1 that sums points scored at Home games.
| A | B | C | |
|---|---|---|---|
| 1 | Home | 21 | ? |
| 2 | Away | 14 | |
| 3 | Home | 28 | |
| 4 | Away | 17 | |
| 5 | Home | 24 |
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:A5 hold stock counts across SKUs. Write a formula in A6 that sums only the counts that are 50 or more.
| A | |
|---|---|
| 1 | 30 |
| 2 | 60 |
| 3 | 45 |
| 4 | 80 |
| 5 | 20 |
| 6 | ? |
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:A5 hold payment methods, B1:B5 hold amounts. Write a formula in C1 that sums payments made by 'check'.
| A | B | C | |
|---|---|---|---|
| 1 | check | 500 | ? |
| 2 | card | 300 | |
| 3 | check | 250 | |
| 4 | cash | 100 | |
| 5 | check | 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 →