Translate Sumifs logic to a working Excel formula

Hi

I have following logic but can't create a sumifs Formula.

D1 Logic=Sumifs(Sum ColumnS IF 
                                        (
                                         the value in cell A1 matches with value in columnN AND 
                                         the value in columnM is "pleted " AND 
                                        (the value in ColumnP is nothing OR "" OR less than or Equal to 200) AND
                                        the value in columnR is (LessThan or EqualTo 6 OR Greater than 8)
                                         )

I have made following formula but it is not working;

=SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,"<="&200,R:R,OR("<="&6,">"&8))

Any clue?

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)

Try

=SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,"<="&200,R:R,{"<=6",">8"})

Try this:

=SUM(SUMIFS(S:S,P:P,{"<=200",""}))

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?

Attach a test file where it isn't working.

PS "Thank you" would be nice ...

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:

=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"},C4:C11,{"Bob Jones";"Andy Garcia"}))

Why not use this?

=SUM(SUMIFS(S:S,N:N,A1,M:M,"pleted ",P:P,{"<=200",""},R:R,"<>5"))

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

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.

Glad to hear it - thanks for letting us know.