Need Formula to display commissions earned per sale when commission rate is tiered

[Urban1323]

Our salesperson makes 5% of every sale until she reaches $20,000 in total sales. At that point the commission jumps to 10% on everything over $20,000. I found a great sum product formula that will tally the total sales commission correctly and will tally individual sales until the tier level switch (see below)
=SUMPRODUCT((B10<=$C$4:$C$5)*(B10>$B$4:$B$5)*(B10-$B$4:$B$5)*$D$4:$D$5)+SUMPRODUCT(((B10>$C$4:$C$5)*($C$4:$C$5-$B$4:$B$5))*$D$4:$D$5)

I need a way to show commissions earned per sale. The cells outlined in red show where the above formula stops working.

Appreciate any help I can get on this. Thanks in advance!

 

[StephenCrump]

Welome to the Forum!

Here's one way to do it:

	A	B	C
1		Rate	Threshold
2	Rate1	5%
3	Rate2	10%	20,000
4
5	Total	Sale	Commission
6	10,000	10,000	500
7	12,000	2,000	100
8	15,000	3,000	150
9	19,000	4,000	200
10	25,000	6,000	550
11	32,000	7,000	700
Sheet1

Cell Formulas
Range	Formula
A6	A6	=B6
A7:A11	A7	=A6+B7
C6:C11	C6	=Rate1*A6+(Rate2-Rate1)*MAX(0,A6-Threshold)-SUM(C$5:C5)

Named Ranges
Name	Refers To	Cells
Rate1	=Sheet1!$B$2	C6:C11
Rate2	=Sheet1!$B$3	C6:C11
Threshold	=Sheet1!$C$3	C6:C11

 

[Urban1323]

Works perfect! Thank you. I never cease to be impressed with the brain power on this forum.