When a project mixes roles at different rates, the blended rate is the hours-weighted average — total cost over total hours. SUMPRODUCT gives one clean number to quote.
The example
Senior 20h@$150, Junior 30h@$80.
| A | B | |
|---|---|---|
| 1 | Role | Hours · Rate |
| 2 | Senior | 20 · $150 |
| 3 | Blended | → $108/hr |
The formula
The formula:
How it works
How it works:
SUMPRODUCT(hours, rates)totals each role’s cost (hours × its rate).- Divide by
SUM(hours)for the hours-weighted average rate. - The blend leans toward whichever role logs more hours — not a simple average of rates.
- Quote the blended rate for simplicity, or itemize roles for transparency.
Blended ≠ average of rates. A senior at $150 and a junior at $80 don’t blend to $115 unless the hours are equal. With 20 senior and 30 junior hours, the blend is $108 — pulled toward the junior because they work more hours. Always weight by hours, which is exactly what SUMPRODUCT/SUM does.
Try it: interactive demo
Hours and rates (comma-separated).
Variations
Total project cost
Before dividing:
Simple average (wrong)
Ignores hours:
Bill at blended rate
Quote total:
Pitfalls & errors
Weight by hours. A plain AVERAGE of rates ignores who works more — use SUMPRODUCT/SUM.
Match ranges. Hours and rates must align in length.
Zero hours. No hours gives #DIV/0!.
Practice workbook
Frequently asked questions
How do I calculate a blended team rate in Excel?
Why not just average the rates?
How do I quote with a blended rate?
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