How to calculate tiered commission with IF in Excel
Formula (Excel and Google Sheets)
=B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)Pick the rate with IFS, checking the highest tier first, and multiply: =B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2).
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
IFS returns the rate for the first condition that is TRUE, so the tiers are checked from the top down: 100,000 and up gets 7%, 50,000 and up 5%, and TRUE catches everything else at 3%. The chosen rate is multiplied by the whole sales amount. Dev, on exactly 100,000, reaches the top tier.
=B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)Result1260
B2$42,000The value in B2.
$E$4$100,000The value in E4.
B2>=$E$4FALSEThe >= operator tests at least.
$F$47%The value in F4.
$E$3$50,000The value in E3.
B2>=$E$3FALSEThe >= operator tests at least.
$F$35%The value in F3.
$F$23%The value in F2.
IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)0.03IFS: Returns the value for the first true test.- test1
B2>=$E$4FALSE - value1
$F$47% - test2, value2
B2>=$E$3FALSE - test2
$F$35% - value2
TRUE - test3
$F$23%
- test1
B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)1260The * operator multiplies.
In Google Sheets
The same formula works in Google Sheets: =B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2*XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1) | $1,260, $2,500, $4,375… | Looks the rate up in the tier table with "exact or next smaller". Adding a tier is a new row, not a longer formula. |
| =B2*IF(B2>=$E$4,$F$4,IF(B2>=$E$3,$F$3,$F$2)) | $1,260, $2,500, $4,375… | Nested IFs, for Excel 2016 and older. |
Common mistakes
- Check the highest tier first. Testing
B2>=$E$3beforeB2>=$E$4gives 5% to everyone over 50,000, including Dev and Eli. - This pays one rate on the whole amount. If each slice of sales earns its own rate, like tax brackets, use the formula in the questions below.
- Enter rates as 5% or 0.05. Typing 5 multiplies sales by five.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate tiered commission with IF
C2, calculate the commission: sales times the rate of the highest tier in E2:F4 the sales reach. Fill down to C7.Questions
How do I pay each slice of sales at its own rate?
Add the slices: =MIN(B2,$E$3)*$F$2+MAX(0,MIN(B2,$E$4)-$E$3)*$F$3+MAX(0,B2-$E$4)*$F$4. The first 50,000 always earns 3%.
How do I add a flat bonus at a target?
Add an IF to the result: =B2*IFS(…)+IF(B2>=100000,500,0).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.