SUMPRODUCT practice exercises
5 graded problems with realistic data, all hard.
Multiplies two (or more) ranges element by element, then sums the results, all in one formula. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the SUMPRODUCT guide first: syntax, a worked example and the mistakes people make.
In this set
- Total cost from quantity × price
- Total portfolio value
- Total labor cost from hours × rate
- Total profit from units × margin
- Total weighted budget allocation
The exercises
Each item has a quantity and a unit price. Find the total cost across all items in one formula, with no helper column.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total Cost: | ? |
| 2 | Pens | 10 | 1.2 | ||
| 3 | Pads | 4 | 3.5 | ||
| 4 | Clips | 20 | 0.5 |
Enter a formula in E1.
Show a hint
SUMPRODUCT(range1, range2) multiplies each pair of cells and sums the results: =SUMPRODUCT(B2:B4,C2:C4).
Without SUMPRODUCT, this needs a helper column (Qty×Price per row) before you can SUM it. SUMPRODUCT skips the helper column entirely.
A small portfolio lists shares held and price per share for each of four holdings. Find the total portfolio value.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Holding | Shares | Price | Portfolio Value: | ? |
| 2 | Stock A | 50 | 12 | ||
| 3 | Stock B | 30 | 8 | ||
| 4 | Stock C | 20 | 25 | ||
| 5 | Stock D | 15 | 10 |
Enter a formula in E1.
Show a hint
One more holding than the last example, same idea: =SUMPRODUCT(B2:B5,C2:C5).
Shares × price, summed across holdings, is literally how a portfolio's value is calculated. This is a real finance formula, not a toy example.
Three workers each belong to a department and logged different hours at different hourly rates. Find the total labor cost.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Worker | Dept | Hours | Rate | Total Labor Cost: | ? |
| 2 | W1 | Sales | 8 | 15 | ||
| 3 | W2 | Sales | 6 | 18 | ||
| 4 | W3 | Ops | 10 | 12 |
Enter a formula in F1.
Show a hint
The Dept column is just along for the ride; multiply Hours by Rate: =SUMPRODUCT(C2:C4,D2:D4).
Payroll and staffing-cost models are built on exactly this hours × rate, summed pattern.
Each product sold a number of units at a per-unit margin (row 2 just notes the quarter). Find the total profit across all products.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quarter | Q3 | Total Profit: | ? | |
| 2 | Product | Units | Margin | ||
| 3 | P1 | 100 | 2.5 | ||
| 4 | P2 | 60 | 4 | ||
| 5 | P3 | 40 | 3 |
Enter a formula in E1.
Show a hint
=SUMPRODUCT(B3:B5,C3:C5).
Profit contribution by product, summed, is a core piece of any margin analysis.
Each of four departments has a weight and a base budget (row 1 just notes the quarter). Find the total weighted budget across departments.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Quarter | Q3 | Weighted Total: | ? | ||
| 2 | Dept | Weight | Base Budget | |||
| 3 | Sales | 0.4 | 10000 | |||
| 4 | Ops | 0.35 | 8000 | |||
| 5 | Marketing | 0.2 | 6000 | |||
| 6 | Support | 0.05 | 4000 |
Enter a formula in F1.
Show a hint
=SUMPRODUCT(C3:C6,D3:D6).
Weighted allocations like this show up constantly in budget modeling and in weighted-average calculations generally.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the SUMPRODUCT exercises →