In this article, we will learn to calculate the sales commission formula in Excel. A sales commission is a type of compensation that is awarded to a person or salesman based on the generated sales. In Microsoft Excel, we can create a sales commission formula and find the amount easily. Today, we will demonstrate 3 easy methods. Using these methods, one can easily determine the sales commission formula in Excel. So, without any delay, let’s start the discussion.
Table of Contents hide
How to Calculate Sales Commission Formula in Excel: 3 Easy Ways
1. Calculate Sales Commission with Simple Formula in Excel
2. Combine IF and VLOOKUP Functions to Calculate Sales Commission
3. Apply Nested Excel IF Function to Create Formula for Determining Sales Commission
Things to Remember
Conclusion
Related Articles
How to Calculate Sales Commission Formula in Excel: 3 Easy Ways
1. Calculate Sales Commission with Simple Formula in Excel
In the first method, we will calculate the sales commission with a simple formula in Excel. To explain the method, we will use a dataset that contains information about the sales amount of January and February of some salesmen. They sold different products in these two months and each product has a different commission rate. Here, the salesmen will also get a base commission of 2 %. So, with all these pieces of information, we will try to calculate the sales for January and February.
Let’s follow the steps below to learn the sales commission formula.
STEPS:
- In the first place, select Cell G6 and type the formula:
=E6*($D6+$C$12)
Here, the general form of the formula can be written as:
=Sales Amount*(Commission Rate+Base Rate)
In the formula, we have locked Cell C12 using the dollar ($) sign in front of both column index and row index. Also, locked the column index of Cell D6.
- Secondly, press Enter and drag the Fill Handle down to get the sales commission for January.
- Thirdly, drag the Fill Handle to the right to find the sales for February.
- After that, you will find the sales commission for January and Februarytogether.
- Now, if we have a Bonus Factor for each month, then, we need to multiply it with the previous formula.
- So, you need to apply the below formula in Cell G6:
=E6*($D6+$C$12)*G$12
Here, we have multiplied the bonus factor. In the formula, we have locked the row index of Cell G12 using the dollar ($) sign.
- In the following step, hit Enter and drag the Fill Handle right to copy the formula.
- Again, use the Fill Handledown.
- Finally, you will see the sales commission for the two months.
Read More: How to Calculate Bonus in Excel
2. Combine IF and VLOOKUP Functions to Calculate Sales Commission
We can calculate the sales commission in Excel by Combining the IF function and the VLOOKUP function. We will use the IF function to test logic and the VLOOKUP function to look for the commission rates in a table. Here, we will use a dataset that contains information about the Sales and Target amount of some salesmen. If a salesman achieves or equals Target, then he will get a commission. Otherwise, he won’t get any commission. Also, each tier has a different commission rate.
Let’s observe the steps to know how we can use the combination of the IF and VLOOKUP functions to calculate sales commission.
STEPS:
- First of all, select Cell F5 and type the formula:
=IF(D5>=E5,VLOOKUP(C5,$B$12:$C$14,2,FALSE)*D5,"Target Not Filled")
In the formula,
- The logic is D5>=E5. That means the Sales amount will have to be greater than or equal to the Targetamount.
- The second argument of the formula is VLOOKUP(C5,$B$12:$C$14,2,FALSE)*D5. It means if the Sales amount is greater than or equal to the Target amount, then the formula will look for the Commission Rate of tier X in the Lookup Table and multiply it with the Salesamount.
- And, if a salesman fails to achieve the Target amount, then it will show Target Not Filled.
- After that, press Enter and drag down the Fill Handle.
- In the end, you will see results like the picture below.
Read More: Excel Formula to Calculate Sliding Scale Commission
3. Apply Nested Excel IF Function to Create Formula for Determining Sales Commission
Another way to calculate the sales commission is to apply the nested IF function in Excel. The nested function generally indicates the use of a function inside another function. To explain the method, we will use a different dataset. In the dataset, you can see the Sales amount of Mike, John, and Tom. Each salesman receives a different commission rate. The Commission Rate table is used here just to show the commission rate. We will not use it inside the formula. It will make the formula easier.
Let’s pay attention to the steps below to know more.
STEPS:
- Firstly, type the formula Cell E5:
=IF(B5="Mike",(D5*0.1),IF(B5="John",(D5*0.15),IF(B5="Tom",(D5*0.25))))
Here,
- If Cell B5 is Mike, then Cell D5 will be multiplied by 0.1. Because the commission rate for Mike is 10 %.
- If it is John then it will be multiplied by 0.15.
- Otherwise, it will be multiplied by 0.25.
- In the second step, drag the Fill Handledown.
- After that, you will see the sales commission like in the picture below.
- To calculate the sum of the sales commission easily, we can use the help of the Pivot Tablefeature.
- For that purpose, select the dataset.
- Then, go to the Insert tab and click on PivotTable.
- A message box will pop up.
- Click OK to proceed.
- Instantly, a new sheet will appear.
- You will find the PivotTable Fields on the right side of the excel workbook.
- Select Salesman and Sales Commission from there.
- The Salesman will be in the ‘Rows’ section and the Sales Commission will be in the ‘Values’ section.
- After selecting them, you will see the Sum of Sales Commission in the new sheet.
- Similarly, you can add the Sales from the PivotTable Fields to get the sum of the total sales.
- Finally, you will see the sum of both the Sales Commission and Salesamount.
Things to Remember
There are some things you need to remember when you are trying to calculate the sales commission formula in Excel.
- In Method-1, use the absolute cell reference carefully. Because if you don’t, then the formula won’t work correctly.
- In Method-2, lock the Lookup Table. Otherwise, you may get different errors.
- Be extra careful when you are using the Nested IF formula in Method-3. Because it becomes lengthy and difficult to apply the parentheses correctly.
Download Practice Book
Download the practice book from here and exercise it to test your skills.
Calculate Sales Commission Formula.xlsx
Conclusion
In this article, we have demonstrated 3 easy ways to Calculate Sales Commission Formula in Excel. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to calculate the sum of the sales commission in Method-3. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise.Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- How to Calculate Bonus Percentage in Excel
- Bonus Calculation as per Bonus Act in Excel
- How to Calculate Tiered Commission in Excel
- How to Calculate Commission in Excel Using VLOOKUP
What is ExcelDemy?
ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
As an Excel expert with a deep understanding of spreadsheet functionalities and advanced formula applications, I can confidently provide insights into the methods presented in the article on calculating sales commission formulas in Excel.
Method 1: Calculate Sales Commission with Simple Formula in Excel
The first method involves using a straightforward formula to calculate sales commissions based on a dataset with sales amounts, commission rates, and a base commission. The formula is expressed as follows:
=Sales Amount * (Commission Rate + Base Rate)
The article explains the steps of locking specific cells using dollar signs to ensure proper referencing. It also introduces the concept of incorporating a bonus factor into the formula for each month.
Method 2: Combine IF and VLOOKUP Functions to Calculate Sales Commission
The second method combines the IF function with the VLOOKUP function to calculate sales commissions based on sales and target amounts. The formula checks if the sales amount is greater than or equal to the target amount and uses VLOOKUP to find the corresponding commission rate in a table. The formula is structured as follows:
=IF(Sales Amount >= Target Amount, VLOOKUP(Salesman, Commission Table, 2, FALSE) * Sales Amount, "Target Not Filled")
This method introduces logical testing and lookup functions to determine whether a salesman is eligible for commission.
Method 3: Apply Nested Excel IF Function to Create Formula for Determining Sales Commission
The third method involves using nested IF functions to determine sales commissions based on different commission rates for different salesmen. The formula is expressed as follows:
=IF(Salesman = "Mike", Sales Amount * 0.1, IF(Salesman = "John", Sales Amount * 0.15, IF(Salesman = "Tom", Sales Amount * 0.25)))
This method showcases the use of nested IF functions to handle multiple conditions efficiently.
Things to Remember
The article provides important considerations when implementing these methods:
- Method 1 emphasizes the careful use of absolute cell references.
- Method 2 advises on the importance of locking the lookup table to avoid errors.
- Method 3 cautions users about the complexity of nested IF formulas and the need to apply parentheses correctly.
Conclusion
In conclusion, the article demonstrates three effective ways to calculate sales commission formulas in Excel, catering to different scenarios and requirements. The inclusion of practical examples and downloadable practice material enhances the learning experience for users of varying skill levels.
For those interested in further practice, the article offers a downloadable practice book, reinforcing the commitment of ExcelDemy Learn Excel & Excel Solutions Center to providing valuable resources for Excel professionals and businesses. If you have any questions or suggestions, the comment section is open for discussion.