Sumifs <> not operating as an AND function

Question

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.

col3 is always b, so your second criterion always fails, hence the 0. — Rory
So then the formula evaluates the formulas on a criteria by criteria basis, not summing as a true AND? I was under the impression my formula would sum where col2 <> a AND where col3 <> b. I understand that all in col3 is b, but I thought by applying the first criteria this changes? — lilacskateteam
No, it is a true AND. So each row has to meet both criteria and none of your rows match the second criteria. What it doesn't do is combine the two columns and the two criteria and then do a single comparison. — Rory

virolino virolino · Accepted Answer · 2019-07-15T09:16:48

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.

Sumifs <> not operating as an AND function

1 Answers