# Tiered Sales Commission in Excel: One SUMPRODUCT, and the Cliff to Avoid

A progressive tier structure pays $4,950 where a cliff structure pays $6,650 on the same $95,000. Here is the one-formula build for both, and the $2 sale that earns a rep $1,200 when the tiers are cliffs.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-tiered-commission-sumproduct
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## Two Structures That Look Identical on Paper

A commission plan with tiers can mean two completely different things, and plan documents are often ambiguous about which. Either the whole of sales is paid at the rate for the tier reached, or each portion is paid at its own tier rate.

The first is a cliff and the second is progressive. On $95,000 of sales they differ by $1,700, and the cliff version contains an incentive so perverse that it is worth showing before anything else.

Tool: [Try the commission calculator](https://dothecalculation.com/calculators/commission-calculator) — Model a tier structure and see what a rep earns at any sales level.

## The plan

**Four tiers**
| Sales from | Rate | Rate increment |
| --- | --- | --- |
| $0 | 3% | 3% |
| $25,000 | 5% | 2% |
| $60,000 | 7% | 2% |
| $120,000 | 10% | 3% |

The third column is the increase in rate at each threshold. It is not in the plan document and it is what makes the one-line formula work.

## The progressive formula

**Progressive commission in one cell**

```
=SUMPRODUCT(--(Sales>Floors), (Sales−Floors), RateIncrements)
```
- On $95,000: 95,000×3% + 70,000×2% + 35,000×2% = 2,850 + 1,400 + 700 = $4,950
- The double minus converts TRUE and FALSE into 1 and 0.
- Tiers not yet reached contribute zero, so the formula needs no IF and no nesting.

**The same answer computed band by band**
| Band | Amount in band | Rate | Commission |
| --- | --- | --- | --- |
| $0 to $25,000 | $25,000 | 3% | $750 |
| $25,000 to $60,000 | $35,000 | 5% | $1,750 |
| $60,000 to $95,000 | $35,000 | 7% | $2,450 |
| Total | $95,000 | — | $4,950 |

> **Always verify a SUMPRODUCT against the long form once** — The one-line version is compact and unreadable. Build the band-by-band table alongside it the first time, confirm the two agree, then hide the long form. If the two ever diverge, the increments column is wrong, which is the only thing that can break.

## The cliff version, and why it misbehaves

**Cliff commission**

```
=Sales * XLOOKUP(Sales, Floors, Rates, , −1)
```
- On $95,000: 95,000 × 7% = $6,650, which is $1,700 more than the progressive structure.
- Match mode −1 finds the tier the sales figure falls into.
- Simpler to compute and far worse to live with.

**What a cliff does at a threshold**
| Sales | Rate | Commission | Extra commission per extra dollar |
| --- | --- | --- | --- |
| $59,998 | 5% | $3,000 | — |
| $60,000 | 7% | $4,200 | $1,200 for $2 of sales |
| $119,998 | 7% | $8,400 | — |
| $120,000 | 10% | $12,000 | $3,600 for $2 of sales |

A rep sitting at $59,000 in the last week of a quarter has an enormous incentive to close anything at any price, or to hold a deal back if they are at $61,000 and next quarter looks thin. Both behaviours are rational responses to the plan, and both are expensive.

> **If you inherit a cliff plan** — Model the payout curve before proposing a change. A progressive structure that matches total cost at the average rep's performance will pay less to top performers and more to the middle, and that redistribution is the conversation to have. The formula change takes a minute; the plan change takes a quarter.

## Quota, accelerators, and draws

Most real plans layer three more mechanics on top. Each is one more term in the same formula.

**Three common additions**
| Mechanic | Formula |
| --- | --- |
| Commission only above quota | =SUMPRODUCT(--(MAX(0,Sales−Quota)>Floors), (MAX(0,Sales−Quota)−Floors), Incs) |
| Accelerator above 100% of quota | Add a tier floor at the quota value with a higher increment |
| Recoverable draw | =MAX(0, Commission − DrawTakenYTD) |
| Non-recoverable draw | =MAX(Commission, DrawAmount) |
| Capped commission | =MIN(Commission, Cap) |

The draw distinction matters more than the formula suggests. A recoverable draw is an advance the rep repays out of future commission; a non-recoverable draw is a floor on earnings. The two formulas differ by one function and the plans differ by thousands of dollars in a bad quarter.

## Laying out the full sheet

**One row per rep per period**
| Column | Contents |
| --- | --- |
| Rep | Name or ID |
| Period | Month or quarter |
| Sales | =SUMIFS over the deal table |
| Quota | From a rep table, looked up |
| Attainment | =Sales/Quota |
| Commission | The SUMPRODUCT |
| Effective rate | =Commission/Sales |
| Draw applied | From the draw rules above |
| Payable | =Commission − DrawRecovered |

The effective rate column is the one to watch. On the progressive plan at $95,000 it is 5.2%, well below the 7% headline tier. Reps read the tier rate and expect the headline; showing the effective rate on the statement prevents a monthly argument.

Tool: [Try the rate calculator](https://dothecalculation.com/calculators/freelance-rate-calculator) — Work backwards from a target income to the sales or billing rate that supports it.

## Errors to watch for

- Using tier rates instead of rate increments in the SUMPRODUCT. It produces a number roughly twice as large and the formula gives no error.
- Floors that overlap or are unsorted. The increments approach assumes ascending floors, and unsorted floors give silently wrong answers.
- Computing commission on revenue where the plan says gross profit. On a discounted deal these differ substantially, and the plan document is the authority.
- Ignoring clawbacks. Commission paid on a deal that later cancels or goes unpaid needs a negative adjustment row, not an edit to the original period.
- Applying quarterly tiers to monthly sales. If tiers reset quarterly, the calculation must run on quarter-to-date sales, and monthly payouts are true-ups against that.

## What the sheet does not decide

- Whether the plan is affordable. Model total payout across the full range of team performance, not at plan. A plan that works at 100% attainment and pays 40% of gross margin at 140% is a problem waiting for a good quarter.
- What behaviour the plan produces. Any threshold creates timing incentives at period end, and progressive tiers reduce but do not remove them.
- When commission is earned versus paid, which is a contract question with real consequences for a rep who leaves mid-cycle.
- How split deals and team credit are handled, which is where most commission disputes actually originate.
- Whether the quota is achievable. A plan built on a quota nobody hits pays nothing and retains nobody, and the formula will report that faithfully.
- Tax and payroll treatment of commission, which is often withheld differently from salary.

**How do I calculate tiered commission in one formula?**

Use =SUMPRODUCT(--(Sales>Floors), (Sales−Floors), RateIncrements), where the increments are the change in rate at each tier rather than the tier rates themselves. Tiers not reached contribute zero automatically.

**What is the difference between progressive tiers and a cliff?**

Progressive pays each portion of sales at its own tier rate. A cliff pays all sales at the rate of the tier reached. On $95,000 with the plan above, progressive pays $4,950 and a cliff pays $6,650. Read the plan document carefully, because both are written as "tiers".

**Why is my SUMPRODUCT commission about double what it should be?**

You are almost certainly using the tier rates rather than the increments. The formula adds a layer at each threshold, so it needs the increase in rate, not the new rate. With the plan above, the increments are 3%, 2%, 2%, and 3%.

**How do I add a quota so commission only starts above it?**

Replace Sales with MAX(0, Sales − Quota) everywhere in the formula. Everything else works unchanged, and the tiers then apply to the amount over quota rather than to total sales.

**What is the difference between a recoverable and non-recoverable draw?**

A recoverable draw is an advance the rep repays from future commission, so payable is MAX(0, commission minus draws already taken). A non-recoverable draw is a guaranteed minimum, so payable is MAX(commission, draw). The two differ by one function and by a great deal of money in a poor quarter.

**Should commission be on revenue or gross profit?**

That is a plan design question, not a spreadsheet one, and the two diverge sharply whenever reps can discount. Revenue-based commission rewards closing at any price; margin-based commission aligns the rep with the business. Whichever the plan says, make the column header say the same word.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-tiered-commission-sumproduct). Quote freely with attribution and a link to this page._