SUMIFs With Multiple Not Equal To Conditions In a Single Column

[5150]

All,

I need a SUMIFs formula that will use multiple <> (Not Equal To) conditions in a single column. The formula should have the following conditions combined into one formula. Attached is the xl file for review.

Condition1: SUMIF Column C is <>C,D,S
Condition2: SUMIF Column D = N
Condition3: SUMIF Column E = N
Sum Answer: 429

[Tony Valko]

Try this...

=SUMPRODUCT(--ISNA(MATCH(C10:C26,{"C","D","S"},0)),--(D10:D26="N"),--(E10:E26="N"),B10:B26)

[lexusap]

Hi,

or you can use this simple formula of SUMIFS

Please Login or Register  to view this content.

Cheers
Albert



Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[5150]

I like that version. Is it possible to consolidate the last 3 conditions in the SumIf to something like this: C9:C26,{"<>C,D,S"} if so how do you write the formula? I've seen it used before not sure were the <> { } " " symbols go exactly.

[Jonmo1]

Is it possible to consolidate the last 3 conditions in the SumIf to something like this: C9:C26,{"<>C,D,S"}

No.

Because that syntax is processed like an OR
It basically creates 3 sumifs formulas, 1 for <>C, 1 for <>D, 1 for <>S
Then sums the results of each.

How many possible values are there for column C?
If it's not many, you could basically reverse the logic
Instead of NOT = C D and S
Do IS = A B F or G

=SUM(SUMIFS(B9:B26,D9:D26,"N",E9:E26,"N",C9:C26,{"A","B","F","G"}))

[5150]

Yea there's a lot of values otherwise I would reverse the logic like you suggested. I normally use your version of the syntax but since I have so many values, there's no way of efficiently doing it that way. That's why I wanted to try using a <>{"C,D,S"} consolidated type version if it was possible. Is there any other method available on performing this task, so that I can have and learn various options?

Thanks to everyone on this post that assisted me on this, I really appreciate your support.

[Jonmo1]

Is there any other method available on performing this task, so that I can have and learn various options?

Did you try Tony's formula?

[Jonmo1]

Perhaps

=SUMIFS(B9:B26,D9:D26,"N",E9:E26,"N")-SUM(SUMIFS(B9:B26,D9:D26,"N",E9:E26,"N",C9:C26,{"C","D","S"))

[5150]

Perfect. BTW, What's the correct syntax for excluding or including blanks? Is the syntax below the right way to write each one?

Exclude: "<>" & ""
Include: = & ""

[Jonmo1]

Exclude: "<>"
INclude: ""