Calculate sales commission with if

Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this:

• For sales less than $10,000, the commission rate is 10%.
• For sales from $10,000 to $20,000, the commission rate is 15%.
• For sales from $20,000 to $30,000, the commission rate is 20%.
• For sales over $30,000, the commission rate is 25%.

The goal is to calculate a commission for each person using the tiered structure above.

Note: the formulas below use decimal values like 0.1 for 10% but you can use percentages like 10%, 15%, etc. directly inside the formulas if you prefer, and Excel will correctly evaluate the percentage values.

IF function

One way to solve this problem is with the IF function as seen in the worksheet above where the formula in cell D5 is:

=IF(C5<10000,0.1,IF(C5<=20000,0.15,IF(C5<=30000,0.2,0.25)))

This kind of formula is referred to as a "nested if" because we have several IF functions nested inside each other. Here's how the formula works step by step:

=IF(C5<10000,0.1,...)

The first IF statement checks if the sales amount in C5 is less than $10,000. If so, it returns 0.1 as the commission rate. If not, it proceeds to the next IF statement.

IF(C5<=20000,0.15,...)

The second IF statement is nested inside the first. It checks if the sales amount is less than or equal to $20,000. If so, it returns 0.15 as the commission rate. If not, it moves on to the final IF statement:

IF(C5<=30000,0.2,0.25)

The third and final IF statement checks if the sales amount is less than or equal to $30,000. If so, it returns 0.2 as the commission rate. If not, it returns 0.25. Notice the last value is the value returned if none of the previous conditions are met, which only occurs if the sales amount is more than $30,000, the formula returns 0.25 as the commission rate.

For more examples of nested if formulas, see How to use the IF function and 19 tips for nested if formulas.

IFS function

Nested IFs can become complicated and hard to read as more conditions are added because additional conditions result in additional IF statements. The IFS function offers a more streamlined way to manage multiple conditions in Excel because it eliminates the need to nest multiple IF statements together. As a result, the syntax is much simpler:

=IFS(test1,result1,test2,result2,test2,result2,...)

For example, to solve this same problem with the IFS function, you can use the formula below in cell D5:

=IFS(C5<10000,0.1,C5<=20000,0.15,C5<=30000,0.2,C5>30000,0.25)

For more details see: How to use the IFS function.

Calculating commission

Both formulas above calculate the correct commission rate for each sales amount in column B, but they don't actually calculate the commission itself. This is done in a separate formula in cell E5:

=C5*D5

Alternatively, you can calculate the commission rate and the commission all in one formula like this:

=IF(C5<10000,C5*0.1,IF(C5<=20000,C5*0.15,IF(C5<=30000,C5*0.2,C5*0.25)))

This would save space on a crowded worksheet. Personally, I prefer to keep the calculations separate when possible because it makes the formulas simpler and easier to audit for errors.