LinkBack URL
About LinkBacks
Hi
I have following logic but can't create a sumifs Formula.
I have made following formula but it is not working;
Please Login or Register to view this content.
Any clue?Please Login or Register to view this content.
Hi
I have following logic but can't create a sumifs Formula.
I have made following formula but it is not working;Please Login or Register to view this content.
Any clue?Please Login or Register to view this content.
Last edited by caabdul; 06-02-2018 at 05:00 AM.
A single SUMIFS() can only handle and logic -- sum records where all of the conditions are met. Without seeing your data or understanding your desired goal (to evaluate whether a pivot table or some other approach might be preferable), I would add a couple of helper columns [GASP! -- who would dare to suggest helper columns?] to handle the or parts of the logic, then include those helper columns in the SUMIFS(). [Note that I am assuming columns T and U are available for this -- put these helper wherever is convenient for your sheet.]
1) T1 =OR(P1="",P1<=200) copy down
2) U1= OR(R1<=6,R1>8)
3) sumifs cell becomes =SUMIFS(S:S,N:N,A1,M:M,"pleted ",T:T,TRUE,U:U,TRUE)
Originally Posted by shg
Here is a Sumifs with OR logic but I can't get it working for me.
https://exceljet.net/formula/sumifs-...a-and-or-logic
Try
=SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,"<="&200,R:R,{"<=6",">8"})
Life's a spreadsheet, Excel!
Say thanks, Click *
Please check the file. All other conditions work but one does not work which is as follows:
=SUMIFS(S:S,P:P,{"<=200","="""})
So if the value in Col P is "", I get 0 answer. But the correct answer should be 5.
File attached for your reference.
Try this:
=SUM(SUMIFS(S:S,P:P,{"<=200",""}))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
So, for the whole thing, then:
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted",P:P,{"<=200",""},R:R,{"<=6",">8"}))
So, how are we getting on? Did the suggestions work for you?
AliGW
It works alone but doesn't work within whole formula.
I tried
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,{"<=6",">8"}))
Attach a test file where it isn't working.
PS "Thank you" would be nice ...
File attached
Why not use this?
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,"<>5"))
Or you could just follow the instructions in the tutorial correctly!!!
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,{"<=6";">8"}))
Adding another OR criteria
You can add one additional criteria to this formula, but you'll need to use a single column array for one criteria and a single row array for the other. So, for example, to sum orders that are "Complete" or "Pending", for either "Andy Garcia" or "Bob Jones", you can use:
Please Login or Register to view this content.
Last edited by AliGW; 06-02-2018 at 04:19 AM.
Column R value should be greater than or Equal to 6 but less than 8.
Then change the operators:
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,{">=6";"<8"}))
Why not just say =7, though?
Or maybe you want this?
=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,">=6",R:R,"<8"))
Last edited by AliGW; 06-02-2018 at 04:13 AM.
Thank AliGW. This works
But I wonder why there is a comma in P:P,{"<=200",""}
and a semicolon in R:R,{"<=6";">8"}
Because that's what is required to make it work: https://exceljet.net/formula/sumifs-...a-and-or-logic
You need a single column array for one and a single row array for the other.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
EDIT: I've done it for you.
Last edited by AliGW; 06-02-2018 at 04:48 AM.
Yes AliGW you have done a great help.
Glad to hear it - thanks for letting us know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks