Special SUMIF function Not Working as Hoped

Copper 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:
=SUMIF([Username],[@Username],G$2:G2)

 

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

image.png

it is

=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])

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

=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])-[@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)

when

=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 )