Split a shared cost — rent, utilities, overhead — across departments by some driver like headcount or square footage. Each share is that department’s weight divided by the total, times the cost.
The example
$12,000 rent split by headcount.
| A | B | C | |
|---|---|---|---|
| 1 | Dept | Headcount | Share |
| 2 | Sales | 10 | $6,000 |
| 3 | Ops | 6 | $3,600 |
| 4 | Admin | 4 | $2,400 |
The formula
Each department’s share of the cost:
How it works
Allocate in proportion to the chosen driver:
- Pick an allocation driver — headcount, floor area, revenue, usage.
- Each share =
cost × thisWeight / SUM(allWeights). Lock the cost and the total-weight range with$. - The shares always add back to the full cost — a good check.
- Swap the weight column to re-allocate by a different driver without touching the formula.
Verify it ties out: =SUM(shares) should equal the original cost exactly. If rounding each share makes it off by a cent, allocate to the largest department last as a balancing figure.
Try it: interactive demo
Cost + weights (one per line).
Variations
By percentage weights
If weights already sum to 100%:
Equal split
Same share each:
Check it ties out
Should equal the cost:
Pitfalls & errors
Lock the total. Use SUM($B$2:$B$10) absolute, or each row divides by a shifting range and shares won’t add up.
Rounding drift. Rounded shares may not sum exactly to the cost; balance the remainder on one line.
Zero total weight. If all weights are blank/zero, the formula divides by zero — ensure at least one positive weight.
Practice workbook
Frequently asked questions
How do I allocate a shared cost in Excel?
How do I make sure the allocated shares add up?
Can I allocate by something other than headcount?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class