Can I have "does not contain" as a SUMIFS condition?
Hi. I'm trying to write a SUMIFS, and I'm having some trouble. I want to make "does not contain" one of the conditions. I have previously achieved something similar outside of a SUMIF by nesting the CONTAINS formula within the NOT formula, but I can't get it to work here. I think I am doing something wrong with the range. Below is my latest attempt of the formula. This one at least isn't giving an error, but it also isn't spitting out the correct value. Any ideas? Thanks.
=SUMIFS([PO Quantity]:[PO Quantity], [Fabric Fiber Content]:[Fabric Fiber Content], NOT(CONTAINS("Pant", [Fabric Fiber Content]@row)), [Style #]:[Style #], [Style #]@row)
Best Answers

you need to use the @cell reference in your contains formula. See below corrected:
=SUMIFS([PO Quantity]:[PO Quantity], [Fabric Fiber Content]:[Fabric Fiber Content], NOT(CONTAINS("Pant", @cell)), [Style #]:[Style #], [Style #]@row)

It sounds like your criteria is on 2 different ranges is that correct?\
If yes, your formula would look something like this (replace column names and criteria as needed):
=SUMIFS([SumRange]:[SumRange], [Criteria1Range]:[Criteria1Range], NOT(CONTAINS("Criteria", @cell)), [Criteria2Range]:[Criteria2Range], ISBLANK(@cell))
Answers

You can do that with the "not equal" operator. You might see not equal represented by != in other apps but Smartsheet only accepts the <> version:
SUMIFS( range , criterion_range_1, criterion1 , criterion_range_2 , <> "criterion2")
I'm cutting your example formula short to demonstrate:
SUMIFS([PO Quantity]:[PO Quantity], [Fabric Fiber Content]:[Fabric Fiber Content], <>"Pant")
Yanis.

Hi Yanis,
I had tried that previously. For some reason it is still counting when the text (pant) appears as well as where is doesn't.

you need to use the @cell reference in your contains formula. See below corrected:
=SUMIFS([PO Quantity]:[PO Quantity], [Fabric Fiber Content]:[Fabric Fiber Content], NOT(CONTAINS("Pant", @cell)), [Style #]:[Style #], [Style #]@row)

@Leibel Shuchat Thank you so much! That did the trick.

Looking for help with a similar forumla if anyone can advise.
I need a sumifs with two criteria, first "is blank", second is "does not contain". Having a problem getting this working if anyone can help!

It sounds like your criteria is on 2 different ranges is that correct?\
If yes, your formula would look something like this (replace column names and criteria as needed):
=SUMIFS([SumRange]:[SumRange], [Criteria1Range]:[Criteria1Range], NOT(CONTAINS("Criteria", @cell)), [Criteria2Range]:[Criteria2Range], ISBLANK(@cell))

@Leibel S You are a legend, thank you!!!!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!