Apr 25 2019 09:23 AM
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.
Apr 25 2019 10:16 AM
For such table
it is
=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])
in Subtotal column
Apr 25 2019 10:19 AM
@Sergei Baklan Thank you so much!!
Apr 26 2019 12:11 PM
@Sergei Baklan Is there a way to adjust the formula to sum all the rows above, but not include the current row?
Apr 26 2019 01:29 PM
Perhaps the simplest way is to deduct the current row value
=SUMIF(INDEX([Username],1):[@Username],[@Username],[Value])-[@Value]
Apr 26 2019 01:56 PM
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).
Apr 26 2019 02:43 PM
@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) )
Apr 26 2019 03:14 PM
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.
Apr 27 2019 01:32 PM
@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 )