calculating 2 rate of commission by one formula

Hi All Genius,

Appreciated if you can help please, rely on your effort.

I am looking to calculate commission by one formula/function in row 19 of attached file.

example shown in attached file that For the first 2000 units sold during the year, pay a commission of 1.50 per unit. After 2000 units are sold, switch to a commission of 1.25 per unit.
one formula in E19 to calculate commission amount that is then copied across

to clear more for efficient answer until May total Sales units comes to 2440 and in May out of 503 units 63 should be treated under 2000 threshold @1.5 % and 440 at the rate of 1.25%. The correct answer I been told for May is 644.5. Any chance to get cleanest/efficient answer with MIN(MAX, using this functions.


I hope I explained it proper, let me know please if any question

Find attached workbook.

Thanks a lot in advance for help

Kind Regards

maybe this... =IF(SUM($E$18:E18)<=$D$14,E18*$E$14,IF(SUM($E$18:E18)>$D$14,E18*$F$14,""))

Please try at E21

=SUMPRODUCT((SUM($E18:E18)>$C$14:$D$14)*(SUM($E18:E18)-$C$14:$D$14)*($E$14:$F$14-$E$14*{0,1}))-SUM($D21:D21)

if you are saying you want to have either 1.5 or 1.25 below the amounts in row 18, then just adjust mine to this instead...
=IF(SUM($E$18:E18)<=$D$14,$E$14,IF(SUM($E$18:E18)>$D$14,$F$14,""))

I was working that out, but what I don't know is what you would then want in the May cell where there were the 63 and 440. Do you want two commissions listed there? Or the numbers of 63 * 1.5 and 440 * 1.25, what would that cell have in it, because you have a different row for the sales plus commissions.

Please try at E19

=SUMPRODUCT((SUM($E18:E18)>$C$14:$D$14)*(SUM($E18:E18)-$C$14:$D$14),($E$14:$F$14-1)-($E$14-1)*{0,1})-SUM($D19:D19)