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 calculate total order value, calculate total payroll cost, calculate weighted score total.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A3 hold quantities, B1:B3 hold unit prices for three line items. Write a formula in C1 that returns the total value across all line items.
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 10 | ? |
| 2 | 3 | 15 | |
| 3 | 1 | 20 |
Use =SUMPRODUCT(A1:A3,B1:B3).
Right — SUMPRODUCT multiplies each pair of quantity and price, then sums all the results in one formula.
Expected result in C1: 85
A1:A3 hold hours worked, B1:B3 hold hourly rates for three employees. Write a formula in C1 for the total payroll cost.
| A | B | C | |
|---|---|---|---|
| 1 | 40 | 20 | ? |
| 2 | 35 | 25 | |
| 3 | 38 | 18 |
Use =SUMPRODUCT(A1:A3,B1:B3).
Good — SUMPRODUCT is the standard way to compute a total across parallel rate and quantity columns.
Expected result in C1: 2359
A1:A3 hold scores, B1:B3 hold weights for three graded components. Write a formula in C1 for the weighted total.
| A | B | C | |
|---|---|---|---|
| 1 | 90 | 0.5 | ? |
| 2 | 80 | 0.3 | |
| 3 | 70 | 0.2 |
Use =SUMPRODUCT(A1:A3,B1:B3).
Right — SUMPRODUCT multiplies each score by its weight and sums the results in a single formula.
Expected result in C1: 83
A1:A3 hold package weights, B1:B3 hold per-pound rates. Write a formula in C1 for the total shipping cost across all packages.
| A | B | C | |
|---|---|---|---|
| 1 | 5 | 2 | ? |
| 2 | 8 | 1.5 | |
| 3 | 3 | 3 |
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:A3 hold quantities needed, B1:B3 hold cost per unit for three ingredients. Write a formula in C1 for the total recipe cost.
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 3 | ? |
| 2 | 1 | 5 | |
| 3 | 4 | 1.5 |
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:A3 hold vote percentages (decimals), B1:B3 hold district populations. Write a formula in C1 for the total estimated votes.
| A | B | C | |
|---|---|---|---|
| 1 | 0.6 | 1000 | ? |
| 2 | 0.45 | 2000 | |
| 3 | 0.7 | 500 |
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:A3 hold account balances, B1:B3 hold interest rates (decimals). Write a formula in C1 for total interest earned across all accounts.
| A | B | C | |
|---|---|---|---|
| 1 | 1000 | 0.02 | ? |
| 2 | 2000 | 0.015 | |
| 3 | 500 | 0.03 |
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:A3 hold serving counts, B1:B3 hold calories per serving for three foods. Write a formula in C1 for total calories.
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 150 | ? |
| 2 | 1 | 300 | |
| 3 | 3 | 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:A3 hold ad impressions (thousands), B1:B3 hold cost per thousand impressions. Write a formula in C1 for total spend.
| A | B | C | |
|---|---|---|---|
| 1 | 50 | 2 | ? |
| 2 | 30 | 3 | |
| 3 | 20 | 1.5 |
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:A3 hold units stored, B1:B3 hold storage cost per unit across three warehouses. Write a formula in C1 for total storage cost.
| A | B | C | |
|---|---|---|---|
| 1 | 100 | 0.5 | ? |
| 2 | 200 | 0.3 | |
| 3 | 150 | 0.4 |
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 →