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

## Download Practice Book

Download the practice book from here and exercise it to test your skills.

## 3 Easy Ways to Calculate Sales Commission Formula in Excel

### 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**February**together.

- 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 Handle**down.

- Finally, you will see the sales commission for the two months.

**Read More:** **How to Calculate Bonus in Excel (5 Handy Methods)**

### 2. Combine IF and VLOOKUP Functions to Calculate Sales Commission

We can combine the **IF **and **VLOOKUP **functions to calculate the sales commission in Excel. 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**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. - 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 (5 Suitable Examples)**

### 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 Handle**down.

- 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 Table**feature. - 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**Sales**amount.

## 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.

## 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. Visit **the ExcelDemy website** for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

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

Please contact me via email or [email protected]

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

Excelfile to [email protected]. Also, mention what you want to update in theExcelfile. We will try to give the solution as early as possible.Thanks!