How to Calculate Tiered Commission in Excel (3 Methods)

Introduction to Tiered Commission

Sales representatives are motivated by commission rate tiers in a tiered commission system. Unlike flat compensation plans, tiered commission programs encourage sales representatives to meet sales targets. As a seller’s performance improves, their commission grows.

For example, let’s consider a scenario where sales representatives receive a 3% base commission on every deal until they sell a total revenue of $50,000.00 at the start of a new fiscal year. After reaching this threshold, their commission increases to 4% until they sell $100,000.00, and then further increases to 5%, and so on.

This payment structure aims to incentivize sales representatives to meet or exceed their quotas and keep closing transactions. The more sales representatives sell, the more money they earn.


Dataset Overview

Suppose we have a large Excel worksheet containing information about several sales representatives from the Armani Group. The dataset includes the sales representativesnames, sales ranges, percentage of tiered commission, and sales values (given in Columns B, C, E, and F, respectively).

We’ll calculate tiered commissions using three methods: the IF function, VLOOKUP function, and SUMPRODUCT function.

calculate tiered commission in excel


Method 1 – Use IF Function to Calculate Tiered Commission in Excel

Step 1

  • Select a cell (e.g., G5) to apply the IF function.

Use IF Function to Calculate Tiered Commission in Excel

  • Enter the following IF function:
=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

  • The logical test checks if the sales value (F6) falls within the specified ranges (D$6 to D$13).
  • The corresponding commission rate (E$6 to E$13) is applied.
  • The final result is multiplied by the sales value (F6).

  • Press Enter to get the tiered commission (e.g., $450.00).

Use IF Function to Calculate Tiered Commission in Excel

Step 2

  • AutoFill the IF function to other cells in column G.

Use IF Function to Calculate Tiered Commission in Excel


Method 2 – Apply VLOOKUP Function to Calculate Tiered Commission in Excel

Step 1

  • Select a cell (e.g., D16) to apply the VLOOKUP function.

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

  • Enter the following VLOOKUP function in the Formula bar:
=VLOOKUP(D15,C6:E13,3,TRUE)
  • D15 is the lookup value (sales target).
  • C6:E13 is the table array containing sales ranges and commission rates.
  • 3 represents the column index for the commission rate.
  • TRUE enables approximate matching.

  • Press Enter to get the commission rate (e.g., 0.01).

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

Step 2

  • In cell D17, calculate the tiered commission:
=D15*D16

  • Press Enter and you will get the tiered commission of $200.00.

Apply VLOOKUP Function to Calculate Tiered Commission in Excel

  • Now you can verify the VLOOKUP function by changing the sales value (e.g., to $300,000.00) and observing the output.


Method 3 – Calculate Tiered Commission Using SUMPRODUCT Function in Excel

In this method, we’ll apply the SUMPRODUCT function to effectively calculate tiered commission in Excel. Follow the instructions below:

Steps

  • Choose a cell (e.g., G5) where you want to calculate the tiered commission.
  • Enter the following formula in the Formula bar:
=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)
    • The formula combines conditions based on sales ranges and commission rates.
    • It calculates the commission for each tier and sums them up.

Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel

  • After entering the formula, press Enter to get the tiered commission (e.g., $1,249.99).

  • AutoFill the SUMPRODUCT function to the remaining cells in column G.

Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel


Things to Remember

  • #N/A! Error:
    • Occurs when the formula or a function fails to find the referenced data.
  • #DIV/0! Error:
    • Happens when a value is divided by zero (0) or the cell reference is blank.
  • #Value! Error (in Microsoft 365):
    • Appears if you don’t select the proper dimension (e.g., matrix elements are not numbers).

Download Practice Workbook

You can download the practice workbook from here:


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

Get FREE Advanced Excel Exercises with Solutions!
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