Can I have "does not contain" as a SUMIFS condition?

Ariana Arden

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)

Leibel S

@Ariana Arden

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 S

@Lisa B 2022

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))

Yanis Kyr

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.

Ariana Arden

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.

Leibel S

@Ariana Arden

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)

Ariana Arden

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

Lisa B 2022

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!

Leibel S

@Lisa B 2022

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))

Lisa B 2022

@Leibel S You are a legend, thank you!!!!!