sumproduct for bonus

[jw01]

Hi

I am using sumproduct to calculate bonus....the formula works great:
=SUMPRODUCT((Bonus!$C$8:$C$19<=F8)*(Bonus!$E$8:$E$19>=F8),Bonus!$G$8:$G$19)

However, there is a criteria that I do not know how to add to my formula:

If someone makes ABOVE 150% to plan, then their payout is "$11,000 + $100 per % achievement above 150%"

I have attached my sample. you will notice in row 12, the example shows 169.09% to plan or 19.05% over 150% mark, so it should be $100*19.05%

How can i add this to my formula?

thxs for the help!

[jeversf]

bonus_sample_sumprod (1).xlsx

Hi JW,

Does this help (please see attached)?

[jw01]

Hello jeversf

thanks for your response; i ended up doing a similar method to yours

=IF(L8>150%,(SUMPRODUCT((Bonus!$C$6:$C$17<=$L8)*(Bonus!$E$6:$E$17>=$L8),Bonus!$G$6:$G$17))+(((L8-150%)*100)*100)

thxs for your great help!