Total payroll burden across a team (SUMPRODUCT)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
Each person's salary and employer burden rate are on their row in A2:C4. Put the total employer cost — the sum of salary × burden rate — in the Total cell E2, rounded to two decimals.
Burden differs per person, so one division or one average rate gives the wrong number.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Employee | Salary | Burden % | Total employer cost |
| 2 | Ada | 3000 | 0.25 | |
| 3 | Ben | 4000 | 0.22 | |
| 4 | Cleo | 2500 | 0.3 |
Notes
This is a weighted total, which is exactly what SUMPRODUCT does: it multiplies the two columns row by row and adds the results. Summing salaries and multiplying by an average burden rate is the classic wrong answer in payroll reconciliation.
How to approach
Your answer goes in E2.
- Target cell is E2
- Multiply salary by burden on each row
- Then add the rows together
- Shape: =ROUND(SUMPRODUCT(?, ?), ?)
Function guide
FAQ
How do I total payroll burden?
=SUMPRODUCT(salaries, rates). Multiplying each row and adding is the only correct way when rates differ.
Why is the total not salary total × average rate?
A weighted average is not the same as multiplying totals. One person at 30% and one at 20% do not both cost 25%.