=SUMPRODUCT((Sales > Thresholds)*(Sales - Thresholds)*Incremental_Rates) For flat bracket commissions based on total sales, use =Sales * VLOOKUP(Sales, Rate_Table, 2, TRUE). For progressive/marginal tiered commissions (like tax brackets), use the SUMPRODUCT differential rate formula.
How to Calculate Tiered Sales Commission in Excel & Google Sheets
Designing commission structures is essential for incentivizing sales teams. However, calculating commissions in spreadsheets often leads to messy, unmaintainable formulas.
There are two primary compensation models used in business:
- Flat Bracket Commission: Meeting a quota unlocks a single commission percentage applied to total revenue.
- Progressive (Marginal) Tiered Commission: Portions of revenue are paid at increasing rates (identical to US federal income tax brackets).
This guide provides formulas for both models.
Model 1: Flat Bracket Commission (Approximate VLOOKUP)
In a flat plan, reps earn a percentage on total closed revenue based on their tier:
- $0 to $49,999:
5% - $50,000 to $99,999:
8% - $100,000 to $199,999:
12% - $200,000+:
15%
Rate Table Setup (Cells G2:H5):
Make sure column G is sorted ascending:
| Lower Threshold (Col G) | Commission Rate (Col H) |
|---|---|
$0 | 5.0% |
$50,000 | 8.0% |
$100,000 | 12.0% |
$200,000 | 15.0% |
Formula (assuming Total Sales in cell B2):
=B2 * VLOOKUP(B2, $G$2:$H$5, 2, TRUE)Or using modern XLOOKUP:
=B2 * XLOOKUP(B2, $G$2:$G$5, $H$2:$H$5, 0, -1)- Example: If Rep closes
$120,000, formula looks up$100,000threshold $\rightarrow$ returns12%$\rightarrow$ Commission =$14,400.00.
Model 2: Progressive / Marginal Tiered Commission (The SUMPRODUCT Method)
In progressive plans, reps earn higher rates only on the incremental dollars within each bracket:
- Tier 1 ($0 to $50,000):
5% - Tier 2 ($50,000 to $100,000):
8% - Tier 3 ($100,000 to $200,000):
12% - Tier 4 ($200,000+):
15%
If a rep sells $140,000, they earn:
- 5% on first $50,000 =
$2,500 - 8% on next $50,000 ($50k-$100k) =
$4,000 - 12% on remaining $40,000 ($100k-$140k) =
$4,800 - Total Progressive Commission =
$11,300.00
The Differential Rate Table Setup (Cells K2:M5):
| Tier | Lower Bound (Col K) | Marginal Rate (Col L) | Differential Rate $\Delta$ (Col M) |
|---|---|---|---|
| Tier 1 | $0 | 5.0% | =L2 (5.0%) |
| Tier 2 | $50,000 | 8.0% | =L3 - L2 (3.0%) |
| Tier 3 | $100,000 | 12.0% | =L4 - L3 (4.0%) |
| Tier 4 | $200,000 | 15.0% | =L5 - L4 (3.0%) |
The Elegant SUMPRODUCT Formula:
With Sales in cell B2:
=SUMPRODUCT((B2 > $K$2:$K$5) * (B2 - $K$2:$K$5) * $M$2:$M$5)How this works:
(B2 > $K$2:$K$5)returns booleanTRUE/FALSE(1 or 0) for whether sales crossed each tier threshold.(B2 - $K$2:$K$5)calculates the excess dollars above each tier boundary.- Multiplied by the Differential Rate ($M2:M5$), it calculates the exact marginal increment without any
IFstatements.
Rep Performance Comparison Table
| Sales Rep (A) | Actual Sales (B) | Flat Bracket Formula (C) | Flat Payout | Progressive SUMPRODUCT Formula (D) | Progressive Payout |
|---|---|---|---|---|---|
| Alex Rivera | $42,000 | =B2*VLOOKUP(B2,$G$2:$H$5,2,TRUE) | $2,100.00 | =SUMPRODUCT((B2>$K$2:$K$5)*(B2-$K$2:$K$5)*$M$2:$M$5) | $2,100.00 |
| Jordan Lee | $85,000 | =B3*VLOOKUP(B3,$G$2:$H$5,2,TRUE) | $6,800.00 | =SUMPRODUCT((B3>$K$2:$K$5)*(B3-$K$2:$K$5)*$M$2:$M$5) | $5,300.00 |
| Sam Taylor | $140,000 | =B4*VLOOKUP(B4,$G$2:$H$5,2,TRUE) | $16,800.00 | =SUMPRODUCT((B4>$K$2:$K$5)*(B4-$K$2:$K$5)*$M$2:$M$5) | $11,300.00 |
| Morgan Diaz | $275,000 | =B5*VLOOKUP(B5,$G$2:$H$5,2,TRUE) | $41,250.00 | =SUMPRODUCT((B5>$K$2:$K$5)*(B5-$K$2:$K$5)*$M$2:$M$5) | $29,750.00 |
Adding Quota Attainment Multipliers
If reps also earn an accelerators (e.g. 1.25x booster) for hitting $>100%$ of their monthly quota:
= Base_Commission * IF(Actual_Sales >= Quota, 1.25, 1.0)Frequently Asked Questions
What is the difference between Flat Bracket and Progressive Tiered commission?
In a Flat Bracket plan, reaching a higher tier applies the higher rate to 100% of sales. In a Progressive (Marginal) plan, only dollars earned above each threshold are taxed/commissioned at the higher rate.
Why is SUMPRODUCT the best method for progressive commissions?
SUMPRODUCT calculates progressive tiers in a single formula without requiring dozens of nested IF statements or helper columns, making it impervious to tier threshold changes.
Why must the VLOOKUP table be sorted ascending for approximate matching?
When range_lookup is TRUE (or omitted), VLOOKUP relies on binary search. If lower bounds are not in ascending order, the function returns incorrect values.