# 2 SUMIFS NOT equal to 2 variations

✭✭✭✭✭
edited 07/21/22

Hello,

I've tried the below formula but it doesn't work (instead of SUMIFS A AND SUMIFS B),

This works: =SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "<>apple")

This doesn't work: =SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "<>apple") + SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "<>orange")

sample below, the + is duplicating the result.

How can I do both? adding the "OR" I also couldn't figure out:

=SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], OR("<>apple", "<>orange"))

Tags:

• ✭✭✭✭✭
edited 07/22/22 Answer ✓

Does this work for you?

=SUMIFS(Pay:Pay, [Code a]:[Code a], [Code a]@row, [Code b]:[Code b], <>"apple", [Code b]:[Code b], <>"Orange")

or

=SUMIFS(Pay:Pay, [Code b]:[Code b], <>"apple", [Code b]:[Code b], <>"Orange", [Code b]:[Code b], [Code b]@row)

• ✭✭✭✭✭

Try removing the = from the second sumifs

• ✭✭✭✭✭

Right, my error... my issue is the below formula is giving me the duplicate instead of the exact amount.

=SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "<>apple") + SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "<>orange")

• ✭✭✭✭✭
edited 07/21/22

sample below, the + is duplicating the result.

the below also wouldn't work:

=SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], OR("<>apple", "<>orange"))

• ✭✭✭✭✭✭

Can you do a sumif for what it actually is instead of what it isn't that way when you add additional criteria it is still only adding the ones you need and not the ones you don't need.

=SUMIFS(pay:pay, [Code a]:[Code a], [Code a]@row, [code b]:[code b], "banana")

• ✭✭✭✭✭
edited 07/22/22 Answer ✓

Does this work for you?

=SUMIFS(Pay:Pay, [Code a]:[Code a], [Code a]@row, [Code b]:[Code b], <>"apple", [Code b]:[Code b], <>"Orange")

or

=SUMIFS(Pay:Pay, [Code b]:[Code b], <>"apple", [Code b]:[Code b], <>"Orange", [Code b]:[Code b], [Code b]@row)

• ✭✭✭✭✭

Hi @Dan W

Yes! that worked! thanks so much!

• ✭✭✭✭✭

Great! Glad we got it figured out!

• ✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!