This guide will discuss how to calculate tiered commission in Excel using three easy and efficient methods.
Since Excel has several built-in tools and functions, we can perform many calculations easily and quickly. For example, we can easily calculate tiered commissions in Excel using the built-in functions.
Tiered commissions are very common in the field of sales. So salespeople often encounter tiered commissions in their work. Basically, tiered commissions provide an incentive for salespeople to increase or grow their sales figures to earn more commissions.
For instance, let’s say a sales representative has a base rate of 2% from their commissions. Then, the sales representative hit a quota of $10,000 in sales which increased their rate to 3%. After, their commission rate will continue to increase as they hit more quota sales.
So calculating the tiered commission can be difficult. But, Excel makes the process easier with the help of its built-in functions. In this case, we will utilize the IF function, the VLOOKUP function, and the SUMPRODUCT function.
Let’s take a sample scenario wherein we need to calculate tiered commission in Excel.
Suppose you are a branch manager who is in charge of calculating the salary of each sales representative. So you need to calculate the tiered commission of each employee. And this is where you opted to use the SUMPRODUCT function to easily calculate their commission.
Great! Before we move on to a real example of calculating tiered commission in Excel, let’s first learn how to write the SUMPRODUCT function.
The Anatomy of the SUMPRODUCT Function
The syntax or the way we write the SUMPRODUCT function is as follows:
=SUMPRODUCT(array1, [array2])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how to begin any function in Excel.
- SUMPRODUCT() is our
SUMPRODUCTfunction. And this function will return the sum of the products of the selected ranges or arrays. - array1 is a required argument. And this refers to 2 to 255 arrays which we want to multiply and add the components. Additionally, all the selected arrays must have the same dimensions.
- array2 is an optional argument. So this also refers to 2 to 255 arrays which we select to multiply and add together. Also, the selected arrays must have the same dimensions.
Awesome! Now let’s dive into a real example of calculating tiered commission in Excel using three easy and efficient methods.
A Real Example of Using the TEXTSPLIT Function in Excel
Let’s say we have a data set containing information about each sales representative. And we have the names of the sales representatives, their sales range, their tiered commission percentage, and their sales value. So this is what our initial data set would look like:
Then, we can use three functions to calculate the tiered commission of our data set. Firstly, we can use the IF function. And this is also the most effective way to calculate the tiered commission.
So the IF function will check whether a given condition is met. Then, it will return one value if it is TRUE and another value if it is FALSE.
Secondly, we can utilize the VLOOKUP function to calculate the tiered commission. So the VLOOKUP function is used to look for a value in the leftmost column of a table or range. Then, it will return a value in the same row from a column we specify.
How does it work? Firstly, we will use the VLOOKUP function to return the corresponding percentage of a sales commission. Next, we will simply calculate the commission using the formula =sale commission*sales percentage.
Lastly, we can also use the SUMPRODUCT function to calculate tiered commissions in Excel. So the SUMPRODUCT function is used to get the sum of the products of selected ranges of arrays.
Finally, we have calculated the tiered commission. And our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Next, let’s move on and learn the process of how to calculate tiered commission in Excel using the three functions.
How to Calculate Tiered Commission in Excel
In this section, we will explain the step-by-step process of how to calculate tiered commission in Excel using the IF function, the VLOOKUP function, and the SUMPRODUCT function.
1. Firstly, we will use the IF function to calculate the tiered commission. So we will select cell G4 to input the IF function. Then, we will type in the formula “=IF(F4<=D$4,E$4,IF(F4<=D$5,E$5,IF(F4<=D$6,E$6,IF(F4<=D$7,E$7,IF(F4<=D$8,E$8,0)))))*F4”. Lastly, press the Enter key to return the result.
2. Secondly, we will drag the fill handle to apply the formula to the other cells.
3. And tada! We have successfully calculated the tiered commission in Excel using the IF function.
4. Next, we will utilize the VLOOKUP function. For example, we want to calculate the tiered commission of $15,000 in sales. In this case, we will type in the formula “=VLOOKUP(D10, C4:E8,3, TRUE)”. Then, press the Enter key to return the value.
5. Afterward, we will calculate the commission based on the return value from the VLOOKUP function and the sales. So input the formula “=D10*D11”. Lastly, press the Enter key to get the result.
6. And tada! We obtained the tiered commission using the VLOOKUP function.
7. Lastly, let’s try using the SUMPRODUCT function to calculate the tiered commission in Excel. In this case, we will input the formula in cell G4. So type in “=SUMPRODUCT((F4<=D$4:D$8)*(F4>C$4:C$8)*(F4-C$4:C$8)*D$4:D$8)+SUMPRODUCT(((F4>D$4:D$8)*(D$4:D$8-C$4:C$8))*E$4:E$8)”.
Then, press the Enter key to get the result.
8. Next, drag down the fill handle to copy the formula to the other cells.
9. And tada! We have successfully calculated the tiered commission in Excel using the SUMPRODUCT function.
And that’s pretty much it! We have explained how to calculate tiered commission in Excel using three simple and easy methods. Furthermore, we have discussed how to use the IF function, the VLOOKUP function, and the SUMPRODUCT function to calculate the tiered commission.
Now you can choose any of the methods to use when calculating tiered commission in Excel.
Are you interested in learning more about what Excel can do? You can now use the SUMPRODUCT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
