Forum Discussion
Special SUMIF function Not Working as Hoped
For such table
it is
=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])
in Subtotal column
SergeiBaklan Is there a way to adjust the formula to sum all the rows above, but not include the current row?
Perhaps the simplest way is to deduct the current row value
=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])-[@Value]
I ended up trying that myself, but I think my logic is off. I'm helping someone with a ticket list for a raffle they're hosting, where people get tickets for an additional raffle for every X amount they spend. But the raffle is online, so every ticket has to be awarded in order of purchase. Right now my logic is off, because basically I have people being awarded additional tickets because everything is being summed up. So, if they get an additional ticket for every $10 (hypothetically), what I have now is giving them 1 ticket for hitting $10, then 2 tickets for hitting $20, which would be 3 total when they should only have 2. And I can't figure out the logic to subtract the right number of tickets.
Do you know of a way to subtract the amount from the last row where the criteria was found? I've attached the spreadsheet for reference. Basically, I've got a column that divides the total amount spent by the amount needed to get the additional ticket. If I could add a column that would take that calculate amount, and subtract from it the calculated amount from the last row before it for that user, I think that would get me what I need, but I don't know how to get to that specific of a cell in a formula based on a criteria like username (vs. just choosing the cell, as that wouldn't work).
kaseyanne5 , perhaps that could be like this
if ST is subtotal calculate as in first formula,
@AM is current purchase (amount in current row)
X is the sum for which you give extra ticket (n*X = n tickets)
when
=ST - @AM*( INT(ST/X) - INT((ST-@AM)/X) )