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
 Smartsheet Customer Resources
 62.5K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 447 Show & Tell
 29 Member Spotlight
 1 SmartStories
 285 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!