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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.


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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo