add up every 10 in the column and apply bonus

[jitterbug888]

I want the bonus in column J to calculate 10 percent of a given program (product value) in column E for every 10 sales.
so every 10th sale of "program 1" would gain a 10% bonus (of the cost of 10 of those programs)

so every time 10 of program1 are racked up in column E, a 10% bonus will be given to the 10th sale.

meanwhile in M3 the total bonus commission is summed.

can someone help me with this?

bonus10percent.JPG

[davsth]

I think you really need to show what the expected results are, as I can think of several interpretations of what you are asking

[jitterbug888]

hi Dav, ok so in column E I have the 5 programs, and column J would pretty much be empty until there are 10 of a certain program added up in column A below (column E in previous sample above), then when that happens, there will be a 10% bonus applied to the total of those 10 listings from E, however the 10% is calculated from the sum of product values for the total 10 in G. (obviously though they would all be the same cost).

the 10% bonus is only added in column J as a total in the row of the 10th listing of whichever reaches 10 in column E.

so every 10th sale of 1 of the 5 programs gets a 10% bonus commission applied at the 10th sale.

this would be for any of the 5 programs. as the sheet progresses it keeps adding up to the next 10 for each of the 5 programs.

for example here program 1 has reached the 10th sale. so the 10% commission would be applied from the total of the 10 sales

every 10th sale gets 10percent bonus.JPG

ps: I wouldnt mind having a drop down menu for each cell in the program column where the user can choose a program from the drop down...I was also hoping that choice would automatically fill the corresponding "product value" cell with the correct dollar value, but maybe i`m asking too much? >.<

[davsth]

perhaps in J2
=IF(MOD(COUNTIF($E$1:E2,E2),10)=0,SUMIF($E$1:E2,E2,$G$1:G2)*0.1-SUMIF($E$1:E1,E2,$J$1:J1),0)
and drag down

this assumes you only have 1 sale on each line

[jitterbug888]

Dav, awesome thanks works great !!.