How to Calculate Tiered Commission in Excel (3 Easy Methods)

While working with Microsoft Excel, sometimes we need to calculate tiered commission. The tiered commission is given to the sales representatives so that a company earns more revenue. Calculating tiered commission 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.


Download Practice Workbook

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


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.


3 Suitable Ways to Calculate Tiered Commission in Excel

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.

calculate tiered commission in excel


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.

Use IF Function to Calculate Tiered Commission in Excel

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

Use IF Function to Calculate Tiered Commission in Excel

Step 2:

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

Use IF Function to Calculate Tiered Commission in Excel

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


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.

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

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

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

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.

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

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

Read More: How to Calculate Sales Commission Formula in Excel (3 Easy Ways)


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)

Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel

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

Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel


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.


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo