Formula Help - Tiered Annual Commission Structure by Monthly Payout

[K8JXN]

Hello,
I am working on a Commission Calculator for my sales team, and I am hitting a wall on the formula for a Monthly Payout based on an Annual Commission Structure. I'm sure this is a simple fix for one of you Excel Gurus. Please help!

Annual Sales/Commission Rate:
0-100,000 = 10%
100,001-200,000 = 11%
200,001-300,000 = 21%
300,001-350,000 = 25%
350,001- 400,000 = 30%
400,001 + = 35%

Example (July - June Fiscal):
John sells 50,000 in July, he gets 10% commission in July
John sells 75,000 in August, he gets 10% on 50,000 and 11% on the remaining 25,000 for August
John sells 10,000 in September, he gets 11% on that 10,000 in September
John sells 75,000 in October, he gets 11% on 65,000 and 21% on the remaining 10,000 in October
John sells 10,000 in November, he gets 21% on that 10,000 in November

and so on......

I have attached my file if someone would be so kind to help with this formula for Row 14 (Columns B-M). The Rep will manually add their monthly sales in Row 11 (Disregard Row 12)

THANK YOU IN ADVANCE!
K8JXN

[Estevaoba]

You have merged cells in range A28:C33.
Change formula in B14 accordingly:
=VLOOKUP(B11,$A$28:$C$33,3)*B11

Good luck!

[K8JXN]

Thank you for responding! The formula you suggested only applies to individual months that fall into the Rate% Table. I am trying to get a rolling Rate, especially difficult for me to formulate when a rate threshold is crossed.

Example:
July = $50K sold, Commission would be $5,000
August = $75K sold (but $125K Total Annually) Commission would be ($50K at 10%) $5,000 + ($25K at 11%) $2,750 = Total August Commission $7,750
September = $10K Sold ($135K Annual) so this is paid on an 11% Rate = Commission would be $1100
October = $75K Sold ($210K Annual) Commission would be ($65K at 11%) $7,150 + ($10K at 21%) $2,100 = Total October Commission $9,250
November = $10K Sold ($220K Annual) so this is paid on a 21% Rate = Commission would be $2,100

and so on.....

Do you have a formula that would calculate this properly?

I added the document with your formula inserted in Row 14 for reference.
Your formula returns : 5000, 7500, 1000, 7500, 1000....
It should return: 5000,7750,1100,9250,2100....

[Glenn Kennedy]

OMG!! How do you look at that colour scheme!!! Hard on the eyes, or what? I'm renowned at work for using soft pastel shades (Glenn's girly spreadsheets, they're called!!).

I hid some of the garish rows and re-jigged the little table at rows 27-30.

I then used this formula in B12, copied across:

=SUMPRODUCT(--(SUM($B11:B11)>$B$28:$B$30),--(SUM($B11:B11)-$B$28:$B$30),$D$28:$D$30)-SUM($A14:A14)

[K8JXN]

You truly are a selfless person! Thank you for your quick response and making me look like a rockstar! Cheers!

[Glenn Kennedy]

I have just noticed that there is a difference in the tiers in your file and your post (#1). Which is correct?

[K8JXN]

We have multiple % tier structures based on what the sales rep chooses upon hire. I was able tweek and add additional formulas so the rep can manualy change the Rate % to correlate with the correct commissions. Thank you again for your help!

[Glenn Kennedy]

OK. Any problems, just shout...