# How to calculate tiered commission with IF in Excel

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).

**Excel:** `=B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Sales | Commission |  | From | Rate |
| Ana | 42000 |  |  | 0 | 0.03 |
| Ben | 50000 |  |  | 50000 | 0.05 |
| Cara | 87500 |  |  | 100000 | 0.07 |
| Dev | 100000 |  |  |  |  |
| Eli | 126300 |  |  |  |  |
| Fay | 18900 |  |  |  |  |

Formula in C2, filled to C7: `=B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)`

Result: $1,260; $2,500; $4,375; $7,000; $8,841; $567

## 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.

## Other ways

- `=B2*XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1)`: 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))`: Nested IFs, for Excel 2016 and older.

## Common mistakes

- Check the highest tier first. Testing `B2>=$E$3` before `B2>=$E$4` gives 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/tiered-commission-with-if