Total your benefit line items and divide by the number of covered employees to get a per-head cost — the figure for budgeting and benchmarking. SUM over the costs, COUNTA (or a headcount) on the bottom.
The example
$420k of benefits across 35 staff.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Total benefits | 420000 |
| 3 | Employees | 35 → $12,000 |
The formula
The formula:
How it works
How it works:
SUM(benefit_costs)totals every line — health, dental, retirement match, life, and so on.- Divide by the employee count (a number, or
COUNTAof an employee list). - The result is the average annual cost per employee for budgeting and benchmarking.
- Break it down with
SUMIFper benefit type to see which line drives the cost.
Loaded labor cost: add salary to the benefits total before dividing to get fully-loaded cost per employee — the number you need for true project costing and headcount budgeting. A common shortcut is salary × a burden factor (e.g. 1.25–1.4).
Try it: interactive demo
Total benefits and headcount.
Variations
Per benefit type
Break it down:
Fully loaded
Salary + benefits:
As % of payroll
Benefit load:
Pitfalls & errors
Headcount can’t be zero. An empty count gives #DIV/0! — use COUNTA on a real employee list.
Covered vs total. Divide by employees actually enrolled if some opt out, not the whole roster.
Annual vs monthly. Keep all costs on the same time basis before dividing.
Practice workbook
Frequently asked questions
How do I calculate benefits cost per employee in Excel?
How do I get fully-loaded cost per employee?
How do I express benefits as a percentage of payroll?
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