While working with Microsoft Excel, sometimes we need to calculate tiered commissions. The tiered commission is given to the sales representatives so that a company earns more revenue. Calculating tiered commissions in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable ways to calculate tiered commission in Excel effectively with appropriate illustrations.

## Introduction to Tiered Commission

**Sales representatives** are motivated by commission rate tiers in a **tiered commission** system. **Tiered commission **programs, as opposed to flat compensation plans, motivate **sales representatives** to meet sales targets. A seller’s commission grows as their performance improves.

For instance, suppose sales representatives get a **3%** base commission on every deal until they sell **$50,000.00** in total revenue at the start of a new fiscal year. Following that, they’ll make **4%** until they sell **$100,000.00**, at which point they’ll increase to **5%**, and so on.

This form of payment plan is designed to encourage **sales representatives** to fulfill a quota and, ideally, to keep closing transactions in order to meet or surpass their targets. Tiered commission schemes work because the more **sales representatives** who sell, the more money they make.

## How to Calculate Tiered Commission in Excel: 3 Suitable Ways

Letâ€™s assume we have an **Excel **large worksheet that contains the information about several **sales representatives** of **Armani Group**. The name of the **sales representatives,** the **sales range**, the **percentage of tiered commission,** and the **Sales value **by the **sales representatives **are given in Columns **B, C, E**, and **F **respectively. From our dataset, we will calculate a **tiered commission**. We can easily calculate a tiered commission in **Excel **by using the **IF****, ****VLOOKUP****, **and **SUMPRODUCT** functions. Hereâ€™s an overview of the dataset for today’s task.

### 1. Use IF Function to Calculate Tiered Commission in Excel

Undoubtedly, the most effective way to calculate a tiered commission is in Excel using **the IF function**. From our dataset, we can easily calculate a tiered commission. This is a time-saving task also. Letâ€™s follow the instructions below to calculate a tiered commission!

**Step 1:**

- First, select a cell to apply
**the IF function**. From our dataset, we will select cell**G5**for the convenience of our work.

- Hence, write down
**the IF function**in that cell.**The IF function**is,

`=IF(F6<=D$6,E$6,IF(F6<=D$7,E$7,IF(F6<=D$8,E$8,IF(F6<=D$9,E$9,IF(F6<=D$10,E$10,IF(F6<=D$11,E$11,IF(F6<=D$12,E$12,IF(F6<=D$13,E$13,0))))))))*F6`

**Formula Breakdown:**

- Most inside the IF function
**F6<=D$13**is the logical_test,**E$13**is the**value_if_TRUE**, and**0**is the**value_if_FALSE**. Similarly, the rest of**the IF function**returns the same output.

- After that, simply press
**Enter**on your keyboard. As a result, you will get**$450.00**as the output of the**IF**function.

**Step 2:**

- Further,
**autoFill**the**IF**function to the rest of the cells in column**G**which has been given in the below screenshot.

### 2. Apply VLOOKUP Function to Calculate Tiered Commission in Excel

In this portion, we will apply **the VLOOKUP function** to calculate tiered commission in Excel. For instance, we will calculate the tiered commission for the sale target of **$20,000.00**. Letâ€™s follow the instructions below to calculate a tiered commission!

**Step 1:**

- First, select a cell to apply
**the VLOOKUP function**. From our dataset, we will select cell**D16**for the convenience of our work.

- After that, type
**the VLOOKUP function**in the Formula bar.**The VLOOKUP function**in the Formula bar is,

`=VLOOKUP(D15,C6:E13,3,TRUE)`

- Where
**D15**is the**lookup_value**. **C6:E13**is the**table_array**.**3**is the**col_index_num**.**TRUE**is the**approximate match**.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get**0.01**as the output of the**VLOOKUP**function.

**Step 2:**

- Further, select cell
**D17**and write down the below mathematical formula in that cell.

`=D15*D16`

- Again, press
**Enter**on your keyboard. As a result, you will get the tiered commission, and the tiered commission is**$200.00**which has been given in the below screenshot.

- Now, we will check the
**VLOOKUP**function whether works or not. To do that, we will simply change the sales value. We choose**$300,000.00**as the sales value and press**ENTER**. We will get the output of**the VLOOKUP function**given in the screenshot below.

### 3. Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel

Last but not the least, we will apply **the SUMPRODUCT function** to calculate the tiered commission in **Excel**. From our dataset, we can easily and effectively calculate the tiered commission. Letâ€™s follow the instructions below to calculate a tiered commission!

**Steps:**

- First, select cell
**G5**to apply**the SUMPRODUCT function**. After that, type**the SUMPRODUCT function**in the Formula bar.**The SUMPRODUCT function**in the Formula bar is,

`=SUMPRODUCT((F6<=D$6:D$13)*(F6>C$6:C$13)*(F6-C$6:C$13)*E$6:E$13)+SUMPRODUCT(((F6>D$6:D$13)*(D$6:D$13-C$6:C$13))*E$6:E$13)`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get**$1,249.99**as the return of the**SUMPRODUCT**function.

- Hence,
**autoFill**the**SUMPRODUCT**function to the rest of the cells in column**G**.

## Things to Remember

👉 **#N/A!** error arises when the formula or a function in the formula fails to find the referenced data.

👉 **#DIV/0!** error happens when a value is divided by **zero(0)** or the cell reference is blank.

👉 In **Microsoft 365**, Excel will show the **#Value! Error** if you donâ€™t select the proper dimension. The **#Value!** error occurs when any of the elements of the matrices is not a number.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable methods mentioned above to **calculate tiered commission **will now provoke you to apply them in your **Excel** spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

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