The Calculation of commission is one of the basic tasks of Excel applications for a long period of time. Calculating commission based on the sales level is also a very common operation. Both types of problems are taken care of in this article. In this article, we will discuss how you can **calculate **the **commission in Excel **using the **IF function**.

**Table of Contents**hide

**Download Practice Workbook**

## 3 Separate Ways to Calculate the Commission Values in Excel Using IF Function

Below, we have the dataset that contains the commission rate depending on the sales figure on the range of cells **B12:C13. **We just need to calculate the commission value in the range of cells **D5:D8**. And those commission values are calculated using the rates depending on the sales values themselves. To accomplish this, we had to adopt varieties of formulas in this article. In order to avoid any kind of compatibility issue, try to use the **Excel 365** edition.

### 1. Determine Commission with Nested IF Functions

Commission values stored in the range of cells can be retrieved and used to calculate the commission value according to the sales. For this, we used the **Nested IF** functions.

**Steps:**

- The formula will be set up in a way that will calculate the commission based on the sales range. The commission rates are mentioned in the range of cells
**B12:C14**.

- Select cell
**D5**and enter the following formula:

`=IF(C5<10000,C5*$C$12, IF(C5>=10000, C5*$C$13,0))`

- Press
**Enter**after this.

**🔎 Formula Breakdown**

**IF(C5>=10000, C5*$C$13,0)**

In this part of the formula, the** IF** function checks whether the sales value mentioned in cell **C5** is greater than 1000 or not. If the value is greater than or equal to 1000, then it will multiply the value with the commission rate mentioned in cell **C****13.** Otherwise, the **IF** function will simply return a null value.

**IF(C5<10000,C5*$C$12, IF(C5>=10000, C5*$C$13,0))**

This part of the formula will check whether the value mentioned in cell **C5 **is less than **1000 **or not. If the cell value is less than **1000, **then it will multiply the cell value in **C5 **with the commission rate mentioned in cell **C12. **Otherwise, it will check whether the value is greater than 1000 or not, in the next formula argument. That is the output mentioned in the previous step.

- Then drag the
**Fill Handle**to cell**D8**. - Doing this will calculate the commission based on the sales figure.
- And obviously, those commissions depend upon the rate mentioned in the range of cell
**B12:C13**.

**Read More: How to Calculate Bonus Percentage in Excel (Step-by-Step Guide)**

### 2. Combine IF with AND Function to Get Commission Using Multiple Rates

A combination of **IF** and **AND **logical functions are used here to find the commission of the sales figure. This combination allows the use of multiple commission rates based on sales value.

**Steps:**

- Select cell
**D5**and enter the following formula:

`=IF(C5<10000,C5*$C$12,IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0)))`

- Press
**Enter**after this.

**🔎 Formula Breakdown**

**IF(AND(C5<40000,C5>10000),C5*$C$13,0)**

In this part of the formula, the** IF** function checks out whether the sales values mentioned in cell **C5** are greater than 1000 and less than 40000 at the same time. If the value is greater than 10000 and less than 4000, then it will multiply the value with the commission rate mentioned in cell **C13**. Otherwise, the **IF** function will simply return a null value.

**IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0))**

This part of the formula will check whether the value mentioned in cell **C5 **is greater than **40000 **or not. If the cell value is greater than **40000, **then it will multiply the cell value in **C5 **with the commission rate mentioned in cell **C13. **Otherwise, it will return the output value in the previous step.

**IF(C5<10000,C5*$C$12,IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0)))**

Finally, this part of the formula checks out whether the value in cell **C5** is less than 10000 or not

If it is less than 10000 then it will multiply the value in cell **C5 **with the commission value in cell **C12.** If not, then it will jump to the next part of the formula which is in the previous part of the formula.

- After entering the formula, you will notice that the commission of the sales figure is based on the rat mentioned in the range of cells
**B12:C13.**

**Read More: Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)**

### 3. Use IF and VLOOKUP Functions

This combination of** IF** and** VLOOKUP** functions allows us to extract the commission rate with the type value range of the cell efficiently. Later, using those commission rates, we can calculate the total commission.

**Steps:**

- The formula will be set up in a way that will calculate the commission on the sales range. The commission rates are in the range of cells
**B12:C14**.

- Select cell
**D5**and enter the following formula:

`=IF(B5>=C5, B5*VLOOKUP(D5,$B$12:$E$14,3,FALSE),"Target Not Met")`

- Press
**Enter**after this.

**VLOOKUP(D5,$B$12:$E$14,3,FALSE),”Target Not Met”)**

This part of the formula will check whether the value mentioned in cell **D5 **is in the range of cell **B12:E14. **And after finding the match, it will then return the corresponding stored commission value in the number **3 **column in the selected range. **FALSE** set to indicate that we want an exact match in the range of cells.

**IF(B5>=C5, B5*VLOOKUP(D5,$B$12:$E$14,3,FALSE),”Target Not Met”)**

Finally, this part of the formula checks out whether the value in cell **B5** is greater or equal to the target value mentioned in cell **C5. **If it is greater than **C5 **then it will multiply the value in cell **B5 **with the commission value returned in the previous formula**.** If not, then it will jump to the next part of the formula which is in the previous part of the formula.

- Then drag the
**Fill Handle**to cell**D8**. - Doing this will calculate the commission based on the sales figure.
- And obviously, those commissions’ value depends upon the rate mentioned in the range of cell
**B12:C13**.

**Read More: How to Calculate Commission in Excel Using VLOOKUP**

## Conclusion

To sum it up, how we can calculate the commission in Excel using the IF function in three separate ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.