Sales commission spreadsheet with variable commission rates


New Member
Jan 8, 2014

I've been hunting around and can't find the answer to my problem anywhere. Here is the commission spreadsheet I want to make for sales staff:

Sales staff have a software sales target (eg $500,000). Cumulative on sales through the year they receive:

- 2.5% on sales up to 25% of their target,
- 5% on sales between 25% and 75% of their target,
- 7.5% on sales between 75% and 100% of their target,
- 10% on sales over 100% of target.

Simple enough (ish) using LOOKUP right? I used the following tutorial and nearly got there Excel Magic Trick 673: Varying Commission Rate Lookup Formula SUMPRODUCT Amazing Solution!! - YouTube But it gets more complicated! Sales are made up of 'services' sold and 'licences' sold. The percentage commission stated above relate to 'licences' sold when the TOTAL sale is between the relevant percentages of target. All services are paid at 3% commission. Do you see the difficulty?

For example, if a sales member sells $50,000 in January where $10,000 is services and $40,000 is licences, they would receive 3% of the $10,000 and 2.5% of the 40,000 because the total number ($50,000) is less than 25% of their annual target of $500,000. If the same sales rep sells £100,000 later in January or later in the year where $50,000 is services and $50,000 is licences, they would receive 3% of $50,000 for the services sold but the licences is a bit more difficult. The total sales for the year are now $150,000 which is 30% of their annual target. Therefore, they receive 5% of licences on all amounts over a total of 25% of target - 5% on $25,000 and 2.5% on new licences for the remaining amount below the 25% threshold - 2.5% on $25,000.

Can anyone help me at all? I'd like to be able to simply enter the total sale amount and the split between services and licences and the commission be calculated. I could run this monthly or quarterly. Please let me know if you would like a working spreadsheet that I already have downloaded from the link above.

Many thanks,


Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
as I can understand



<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>

Spreadsheet Formulas



Excel tables to the web >> Excel Jeanie HTML 4
Upvote 0

Thanks for your help but it needs to be a little more complicated I'm afraid. Your spreadsheet works very nicely when the commission is paid at the same variable rate for the whole of the sale. My difficulty is that the percentages you've used above are paid on only part of the sale - the licences sold. The services sold are paid at a flat 3%. But, the commission calculation for the licences (e.g up to 25% of target being 2.5%) is based on the TOTAL sold.

I've just realised that I can't attached a spreadsheet to this for anyone to see. Can that be true??
Upvote 0
you can post data in this newsgroup, for which you have to add and addin to your excel. see under sticky in thread list."recommended addins" -- Excel Jeanie

If this is difficult and if your administrator allows you can save the file in and post the download url. check with administrator for solution
Upvote 0
Thank you. I'll try posting my spreadsheet below:

Excel 2003
1HurdleRateServices Rate
9Total Sales (Licences & Services)30,000.0060,000.0020,000.0050,000.0095,000.0060,000.0060,000.0030,000.0095,000.0075,000.0020,000.0050,000.00
11Services proportion10,000.0010,000.0010,000.0015,000.0025,000.0010,000.005,000.000.0055,000.0030,000.005,000.0020,000.00
12Formula 1750.001,500.00500.002,125.004,750.003,000.003,000.002,250.007,125.007,500.002,000.005,000.00
Cell Formulas
Upvote 0
Good, posting the spreadsheet worked (above). So you can see that the row 'formula 1' is calculating the commission based on the amount sold and the variable percentages using the data in row 9. What I want it to do now is also consider the 'services' sold in row 11. Services are paid at 3% commission, a flat rate. The 2.5%, 5%, 7.5% and 10% commission relates only to the licences sold (calculated as total sold minus services sold) and those percentages are applicable when the TOTAL (services and licences combined) is at the amounts specified (up to 125,000 at 2.5%, 1250,000 - 375,000 at 5%, 375,000 - 500,000 at 7.5%, over 500,000 at 10%).

I hope that's better explained now. Anyone got any ideas?
Upvote 0
N.B. The named range rRate is the rate differential.
Row 13 Calculate the commission based on the total


The percentage of sales License/Total is the License comm line 20,000/30000 * total line 13
3% of Services = service commission line
Sum of the two lines is the total commission
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back