CODEWITHSUNDEEP
excelsumifs
lilacskateteam
lilacskateteam

Reputation: 27

Sumifs <> not operating as an AND function

I have two criteria columns with data I want to exclude, but the result of my sumifs is wrong when I enter the two criteria. When I concatenate the two columns and use a sumifs with one criteria (could also use a sumif), then the result is correct.

I would like to sum col1 where col2 is not "a" and where col3 is not "b". The formula I have used is =SUMIFS(A9:A12,B9:B12,"<>a",C9:C12,"<>b") which returns 0.

=SUMIFS(A9:A12,D9:D12,"<>ab") returns 7, which is correct.

I understood that SUMIFS runs on an AND operator so all conditions must be true, but in the first case with two criteria it excludes all of the numbers because everything in col3 is a "b". 

col1    col2    col3    col4
1        a        b      ab
2        b        b      bb
3        a        b      ab
5        d        b      db

Why am I getting different results? When I do the same formula but as inclusive such as =SUMIFS(A9:A12,B9:B12,"a",C9:C12,"b") and =SUMIFS(A9:A12,D9:D12,"ab"), both formulas return 4 which is correct. But using <> provides mismatched answers.

Upvotes: 0

Views: 81

Answers (1)

virolino
virolino

Reputation: 2201

All formulas in your question give correct results.

col1    col2    col3    col4
1        a        b      ab       // a<>a false, b<>b false -> no summing
2        b        b      bb       // b<>a true , b<>b false -> no summing
3        a        b      ab       // a<>a false, b<>b false -> no summing
5        d        b      db       // d<>a true , b<>b false -> no summing

Try to change the second line to:

2        b        e      be       // b<>a true , b<>e true

You will see that the result will change.

Upvotes: 1

Related Questions