Home

Special SUMIF function Not Working as Hoped

%3CLINGO-SUB%20id%3D%22lingo-sub-482831%22%20slang%3D%22en-US%22%3ESpecial%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482831%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20formula%20that%20will%20allow%20me%20to%20sum%20totals%2C%20based%20on%20another%20criteria%20in%20a%20table%20(so%20basically%20a%20SUMIF)%2C%20but%20I%20only%20want%20to%20sum%20totals%20that%20are%20in%20the%20row%20or%20above%20the%20row%2C%20of%20the%20current%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%3A%3CBR%20%2F%3E%3DSUMIF(%5BUsername%5D%2C%5B%40Username%5D%2CG%242%3AG2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20hoped%20this%20would%20help%2C%20as%20with%20each%20row%20the%20end%20point%20of%20the%20range%20would%20change.%20But%2C%20when%20I%20use%20this%20function%2C%20it%20still%20sums%20the%20entire%20column%20G%20based%20on%20the%20criteria%2C%20not%20just%20from%20that%20range.%20A%20normal%20SUM%20function%20works%20the%20way%20it%20should%2C%20but%20SUMIF%20is%20summing%20the%20whole%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487593%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BIs%20there%20a%20way%20to%20adjust%20the%20formula%20to%20sum%20all%20the%20rows%20above%2C%20but%20not%20include%20the%20current%20row%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482873%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482869%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327530%22%20target%3D%22_blank%22%3E%40kaseyanne5%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20243px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110292iC3CC7C8C7505D288%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20is%3C%2FP%3E%0A%3CPRE%3E%3DSUMIF(INDEX(%5BUsername%5D%2C1)%3A%5B%40Username%5D%2C%5B%40Username%5D%2C%5BValue%5D)%3C%2FPRE%3E%0A%3CP%3Ein%20Subtotal%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487832%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327530%22%20target%3D%22_blank%22%3E%40kaseyanne5%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EPerhaps%20the%20simplest%20way%20is%20to%20deduct%20the%20current%20row%20value%3C%2FP%3E%0A%3CPRE%3E%3DSUMIF(INDEX(%5BUsername%5D%2C1)%3A%5B%40Username%5D%2C%5B%40Username%5D%2C%5BValue%5D)-%5B%40Value%5D%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487913%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487913%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ended%20up%20trying%20that%20myself%2C%20but%20I%20think%20my%20logic%20is%20off.%20I'm%20helping%20someone%20with%20a%20ticket%20list%20for%20a%20raffle%20they're%20hosting%2C%20where%20people%20get%20tickets%20for%20an%20additional%20raffle%20for%20every%20X%20amount%20they%20spend.%20But%20the%20raffle%20is%20online%2C%20so%20every%20ticket%20has%20to%20be%20awarded%20in%20order%20of%20purchase.%20Right%20now%20my%20logic%20is%20off%2C%20because%20basically%20I%20have%20people%20being%20awarded%20additional%20tickets%20because%20everything%20is%20being%20summed%20up.%20So%2C%20if%20they%20get%20an%20additional%20ticket%20for%20every%20%2410%20(hypothetically)%2C%20what%20I%20have%20now%20is%20giving%20them%201%20ticket%20for%20hitting%20%2410%2C%20then%202%20tickets%20for%20hitting%20%2420%2C%20which%20would%20be%203%20total%20when%20they%20should%20only%20have%202.%20And%20I%20can't%20figure%20out%20the%20logic%20to%20subtract%20the%20right%20number%20of%20tickets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20of%20a%20way%20to%20subtract%20the%20amount%20from%20the%20last%20row%20where%20the%20criteria%20was%20found%3F%20I've%20attached%20the%20spreadsheet%20for%20reference.%20Basically%2C%20I've%20got%20a%20column%20that%20divides%20the%20total%20amount%20spent%20by%20the%20amount%20needed%20to%20get%20the%20additional%20ticket.%20If%20I%20could%20add%20a%20column%20that%20would%20take%20that%20calculate%20amount%2C%20and%20subtract%20from%20it%20the%20calculated%20amount%20from%20the%20last%20row%20before%20it%20for%20that%20user%2C%20I%20think%20that%20would%20get%20me%20what%20I%20need%2C%20but%20I%20don't%20know%20how%20to%20get%20to%20that%20specific%20of%20a%20cell%20in%20a%20formula%20based%20on%20a%20criteria%20like%20username%20(vs.%20just%20choosing%20the%20cell%2C%20as%20that%20wouldn't%20work).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-488038%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-488038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327530%22%20target%3D%22_blank%22%3E%40kaseyanne5%3C%2FA%3E%26nbsp%3B%2C%20perhaps%20that%20could%20be%20like%20this%3C%2FP%3E%0A%3CP%3Eif%20ST%20is%20subtotal%20calculate%20as%20in%20first%20formula%2C%3C%2FP%3E%0A%3CP%3E%40AM%26nbsp%3Bis%20current%20purchase%20(amount%20in%20current%20row)%3C%2FP%3E%0A%3CP%3EX%20is%20the%20sum%20for%20which%20you%20give%20extra%20ticket%20(n*X%20%3D%20n%20tickets)%3C%2FP%3E%0A%3CP%3Ewhen%3C%2FP%3E%0A%3CPRE%3E%3DST%20-%20%40AM*(%20INT(ST%2FX)%20-%20INT((ST-%40AM)%2FX)%20)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-488101%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-488101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20but%20it%20doesn't%20seem%20to%20being%20doing%20what%20I'm%20looking%20for.%20It's%20just%2C%20kinda%2C%20taking%20X%20out%20of%20the%20total%20for%20some%20of%20the%20cells.%20I'm%20going%20to%20go%20see%20if%20I%20need%20to%20maybe%20change%20up%20my%20format%20and%20revisit%20which%20types%20of%20formulas%20I%20should%20be%20using.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489818%22%20slang%3D%22en-US%22%3ERe%3A%20Special%20SUMIF%20function%20Not%20Working%20as%20Hoped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327530%22%20target%3D%22_blank%22%3E%40kaseyanne5%3C%2FA%3E%26nbsp%3B%2C%20it%20substructs%20not%20X%20but%20amount%20multiplied%20on%20how%20many%20X%20we%20have%20in%20total.%20That's%20how%20I%20understood%20your%20logic%3C%2FP%3E%0A%3CP%3E%3CEM%3EDo%20you%20know%20of%20a%20way%20to%20subtract%20the%20amount%20from%20the%20last%20row%20where%20the%20criteria%20was%20found%3F%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EIf%20subsruct%20only%20one%20current%20amount%20if%20over%20another%20X%2C%20when%20maybe%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DST%20-%20%40AM*(%20(INT(ST%2FX)%20-%20INT((ST-%40AM)%2FX))%26gt%3B0%20)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
kaseyanne5
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:
=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 )

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies