Excel 365 + Google Sheets · IFS
Stop nesting IF inside IF
Checks conditions in order and returns the value of the first one that's true.
The formula
=IFS(B2>=5000, 10%, B2>=2000, 5%, TRUE, 0%)What each part means
B2>=5000, 10%1st ruleB2>=2000, 5%2nd ruleTRUE, 0%Everything else
Example
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Laura | 6,200 | |
| 3 | João | 3,100 | |
| 4 | Priya | 900 | |
| 5 | Tom | 2,000 |
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Laura | 6,200 | 10% |
| 3 | João | 3,100 | 5% |
| 4 | Priya | 900 | 0% |
| 5 | Tom | 2,000 | 5% |
Step by step
- Click cell C2.
- Type the formula:
=IFS(B2>=5000, 10%, B2>=2000, 5%, TRUE, 0%) - Press Enter.
- Drag the small square in the bottom-right corner of the cell down to copy it to the other rows.
Where it works
- Excel 365 and Excel 2021 or newer
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English IFS
- Español SI.CONJUNTO
- Português SES