New Contributor

# SUMPRODUCT - Tiered Commissions - Two Categories of Commissions

Hi, everyone.

I am requesting help with a SUMPRODUCT formula for the following tiered commission situation.

The commission schedule changes at two levels, 90,000 and 150,000, when the Cumulative Company share of Gross Commissions reaches each tier. There is one schedule for Category 1 projects and another schedule for category 2 projects.

I've calculated it manually to show how it should work (I think), but I have not been able to figure out the SUMPRODUCT formula.

 Gross Commission Company Share of Commission Manually Calculated Cumulative Formula? Category 65,000.00 26,000.00 26,000.00 SUMPRODUCT? 2 .4*65000 60,000.00 18,000.00 44,000.00 SUMPRODUCT? 1 .3*60000 75,000.00 30,000.00 74,000.00 SUMPRODUCT? 2 .4*75000 85,000.00 22,533.33 96,533.33 SUMPRODUCT? 1 16000/0.3=53333.33*.3 + 32666.67*.2 80,000.00 24,000.00 120,533.33 SUMPRODUCT? 2 .3*80000 100,000.00 29,822.22 150,355.56 SUMPRODUCT? 2 29466.67/.3=98222.23*.3 + 1777.77*.2

Crossover points Row 5 and Row 7

Tiered Commission Schedule - Company Share

When Company Cumulative Share of Gross Commissions Meets each Tier

 Tier Start Tier End Category 1 Category 2 0 90000 30% 40% 90000 150000 20% 30% 150000 20% 20%

Thank you,

Michael

2 Replies

# Re: SUMPRODUCT - Tiered Commissions - Two Categories of Commissions

Can you explain the logic in words that leads to a calculation like

16000/0.3=53333.33*.3 + 32666.67*.2

Where do all these numbers come from?? How do they relate to the data in that row?

Gross Commission Company Share of Commission Manually Calculated Cumulative
85,000.00               22,533.33           96,533.33

# Re: SUMPRODUCT - Tiered Commissions - Two Categories of Commissions

Hi,

Yes.

When I see that the next commission will bring Cumulative Company commissions above the next tier, in this case, \$90,000, and because I do not have a formula to help me calculate the commission, I must do it manually.

In this case, 16000/0.3=53333.33*.3 + 32666.67*.2 ...

I see from the chart that Cumulative Company Commissions have already reached \$74,000, so another \$16,000 will bring Cumulative Company Commissions to the next tier, \$90,000. I can calculate how much of the current commission of \$85,000 is used up at the below \$90,000 (1st) tier by dividing \$16,000 by the percentage for the 1st tier, 30%, which equals \$53,333,33. Therefore, the remaining commission to be split between the company and the salesperson at the 2nd tier is \$31,666.67 (\$32,666.67 was a miscalc). The 2nd tier rate is 20%, and 20% of 31,666.67 equals \$6,333.33. Adding together the 1st tier Company share of \$16,000 to the 2nd tier Company share of \$6,333.33 gives a total Company share of \$22,333.33 (not 22,533.33 in the original post... my miscalc).

I hope this is the logic you are looking for.

The tiers will only crossover twice per year, once at \$90,000 and once at \$150,000. The fact there are two categories in addition to two tiers really trips me up. I've tried using "if" statements and it works up to the first tier, but when it gets to the second tier the if statements get too complex for me.

Thanks,

Michael