# How to Calculate Sales Commission Formula in Excel (3 Easy Ways)

### Method 1 – Calculate the Sales Commission with a Simple Formula in Excel

We will use a dataset that contains information about the sales amount of January and February of some salespeople. They sold different products in these two months and each product has a different commission rate. The base commission is 3%. So, with all these pieces of information, we will calculate the sales commissions for January and February.

STEPS:

• Select Cell G6 and copy the formula:
`=E6*(\$D6+\$C\$12)`

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.

• Press Enter and drag the Fill Handle down to get the sales commission for January.

• Drag the Fill Handle to the right to find the sales for February.

• You will find the sales commission for January and FebruaryÂ together.

• If we have a Bonus Factor for each month, then, we need to multiply it with the previous formula.
• 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.

• Hit Enter and drag the Fill Handle right to copy the formula.

• Use the Fill HandleÂ down.

• You will see the sales commission for the two months.

### Method 2 – Combine IF and VLOOKUP Functions to Calculate Sales Commissions

We will use a dataset that contains information about the Sales and Target amount of some salespeople. If they achieve the Target, they get a commission. Each tier has a different commission rate.

Steps:

• Select Cell F5 and insert the formula:
`=IF(D5>=E5,VLOOKUP(C5,\$B\$12:\$C\$14,2,FALSE)*D5,"Target Not Filled")`

• The logic is D5>=E5. That means the Sales amount will have to be greater than or equal to the TargetÂ amount.
• 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 SalesÂ amount.
• If a salesman fails to achieve the Target amount, the formula will show Target Not Filled.
• Press Enter and drag down the Fill Handle.

• You will see results like the picture below.

### Method 3 – Apply a Nested Excel IF Function to Create a Formula for Determining Sales Commissions

In this dataset, we have the Sales amounts for 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.

Steps:

• Copy the following formula in Cell E5:
`=IF(B5="Mike",(D5*0.1),IF(B5="John",(D5*0.15),IF(B5="Tom",(D5*0.25))))`

• 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.
• Drag the Fill HandleÂ down.

• You will see the sales commission like in the picture below.

• Select the dataset.

• Go to the Insert tab and click on PivotTable.

• A message box will pop up. Click OK to proceed.

• A new sheet will appear. You will find the PivotTable Fields on the right side of the excel workbook.
• Select Salesman and Sales Commission.
• The Salesman will be in the â€˜Rowsâ€™ section and the Sales Commission will be put in the â€˜Valuesâ€™ section.

• You will see the Sum of Sales Commission in the new sheet.

• You can add the Sales from the PivotTable Fields to get the sum of the total sales.

• You will see the sum of both the Sales Commission and SalesÂ amount.

You can modify the formula in Method 3 to reference specific table values.

<< Go Back to Commission Bonus | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

1. I am from Nigeria and I bought an excel comission sales tracker sheet from Etsy to track and calculate the monthly sales Comissions due to our Free lance comission referral partners

1) Can You enable and extend the sheet to take 1000 or more sales persons instead of the 100 it currently appears
2) Can You make the transaction dates in this exact format and order
29th /September/2022

Or WhatsApp/ Mobile

+2348060638063 to review the excel template and to give me a quote

• Hi STEPHEN,

Thanks for reaching out to us. I am replying to you on behalf of Exceldemy. We will be happy to help you.
Please send the Excel file to [email protected]. Also, mention what you want to update in the Excel file. We will try to give the solution as early as possible.

Thanks!

Advanced Excel Exercises with Solutions PDF