sumproduct and ifs....issue

hello all

struggling or rather wanting to make sure my logic in my formula is functioning correctly from the excel gurus.

basically im trying to modify payout for sales ppl

here is OLD payout breakdown
1st item sold = $50
any additional item = $10
(i.e. if 6 items sold, then (1*$50)+(5*$10)=$100

but NOW
1st item sold = $50
any additional item = $10
if in the month, items sold exceeds 11, then on the 12th item onwards payout is $75 (if month has 4 weeks)...and if month has 5 weeks, every 15th + item is $75

here is my formula
=IF(Q15<12,SUMPRODUCT(--('CSA Detail'!$D$15:$D$414=$P15),--('CSA Detail'!$E$15:$E$414=Q$14),'CSA Detail'!$K$15:$K$414),SUMPRODUCT(--('CSA Detail'!$D$15:$D$414=$P15),--('CSA Detail'!$E$15:$E$414=Q$14),'CSA Detail'!$K$15:$K$414)+(SUMPRODUCT(--($D$15:$D$414=$P15),--($E$15:$E$414=Q$14),--($J$15:$J$414))-VLOOKUP(Q$14,Lookup!$K$2:$M$13,3,FALSE))*'CSA Detail'!$D$8)

in my attached workbook...i have highlighted in red column S13 where i think my calculation is not working

can someone pls help!

hey thx u so much for your great help....the formula works great

just one question/issue

it's working great however
- In the event the rep is able to close more than 12 deals in the month, the bonus for each additional deal will increase from $50 to $75 (this formula works fine)...however..... the multiple service location bonus on the same deal will remain at $10.

i.e.
in P1 I sold 13 deals, however one of the deals has multiple locations (column J) i.e. 2...so if it is more than 12 deals, then it becomes first location is $75 + any additional location reverts back to $10

in the solution, it simply adds $75 + $75 if tehre are two deals...can u pls help!!!

thxs so much!

hello guys

pls see attached workbook

i have managed to make the formula work...however it is giving me an issue whnever column J exceeds greater than 2 locations and assigns the values below a $0 *pls see highlight in red**

ive been stuck on this for the past 4 hours...pls help!

https://skydrive.live.com/redir.aspx...111&parid=root

PLs see link for attachment....my work firewall or something is not letting me upload it on to excelforum for some odd reason

it is much apprecaited! **pls run macro to unprotect file**