Excel track · Exercise 83 of 86 Paid
A headcount and payroll summary
Total a payroll across two columns, then divide it by the headcount.
Advanced
Four teams with headcount and salary, summarised two ways
Read-only data · screen 1 of 2 · answer in D2
In D2, total the payroll the whole sheet implies.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team | Heads | Salary | Answer |
| 2 | Sales | 3 | 45000 | |
| 3 | Support | 2 | 38000 | |
| 4 | Sales | 2 | 41000 | |
| 5 | Ops | 1 | 52000 |
In D2, work out the payroll cost of one head, rounded to a whole number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team | Heads | Salary | Answer |
| 2 | Sales | 3 | 45000 | |
| 3 | Support | 2 | 38000 | |
| 4 | Sales | 2 | 41000 | |
| 5 | Ops | 1 | 52000 |
Reference answer
What it does
SUMPRODUCT multiplies two ranges row by row, then adds the products. Given heads in B2:B5 and salaries in C2:C5, each team's headcount pairs with that team's salary before anything is summed. Three heads at 45000 come to 135000, and the four rows come to 345000.
The second screen divides that payroll by the total headcount, eight, and rounds: 345000 across eight heads is 43125. The divisor is SUM(B2:B5), so a team of three carries three times the weight of a team of one. Averaging the salary column gives 44000, a figure nobody on the sheet is paid.
Both ranges need the same shape; one that starts a row late pairs each salary with the wrong headcount, and the total is still a number. The divisor comes from the heads column: COUNT(B2:B5) counts four rows and would put the cost at 86250.
Syntax
=SUMPRODUCT(heads,salaries) totals a payroll; divide by SUM(heads) for the cost per headWorked example
On Four teams with headcount and salary, summarised two ways, =SUMPRODUCT(B2:B5,C2:C5) returns 345000.
=SUMPRODUCT(B2:B5,C2:C5)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The two ranges are different shapes, so the rows cannot be paired. |
#DIV/0! | The headcount total is zero and the cost per head divides by it. |
Wrong divisor | The divisor counts rows instead of heads, so four teams divide a payroll built on eight. |
At work
Costing a monthly payroll from headcount and salary columns that both move.