Special SUMIF function Not Working as Hoped

Occasional Contributor

I'm trying to create a formula that will allow me to sum totals, based on another criteria in a table (so basically a SUMIF), but I only want to sum totals that are in the row or above the row, of the current cell.


I tried this:


I'd hoped this would help, as with each row the end point of the range would change. But, when I use this function, it still sums the entire column G based on the criteria, not just from that range. A normal SUM function works the way it should, but SUMIF is summing the whole column.

8 Replies

@kaseyanne5 ,


For such table


it is


in Subtotal column

@Sergei Baklan Thank you so much!! 

@Sergei Baklan Is there a way to adjust the formula to sum all the rows above, but not include the current row?

@kaseyanne5 ,

Perhaps the simplest way is to deduct the current row value



@Sergei Baklan 

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)


=ST - @AM*( INT(ST/X) - INT((ST-@AM)/X) )


@Sergei Baklan ,


Thanks, but it doesn't seem to being doing what I'm looking for. It's just, kinda, taking X out of the total for some of the cells. I'm going to go see if I need to maybe change up my format and revisit which types of formulas I should be using. 

@kaseyanne5 , it substructs not X but amount multiplied on how many X we have in total. That's how I understood your logic

Do you know of a way to subtract the amount from the last row where the criteria was found?

If subsruct only one current amount if over another X, when maybe


=ST - @AM*( (INT(ST/X) - INT((ST-@AM)/X))>0 )