SUMIFS and CONTAINS with column criteria, Within CONTAINS can a "Not Equal To" operator be used.

UserNewQuestions · 02/15/24

Hello all!!!

I am trying to modify a working equation "=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, CONTAINS("Quick Win", @cell), AssignedText:AssignedText, CONTAINS("First.Last1", @cell))

The modification that I am attempting is to add another CONTAINS statement to the above. I am trying to add ....StatusText:StatusText, CONTAINS(<>"Complete", @cell). The whole function would then be:

=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, CONTAINS("Quick Win", @cell), AssignedText:AssignedText, CONTAINS("First.Last1", @cell), StatusText:StatusText, CONTAINS(<>"Complete", @cell))

Smartsheet returns "#@Invalid Operation", is this because a different function needs to be used to compare a "Text String" i.e.: "Complete". I have tried different options but no luck.

Any assistance would be gratefully appreciated.

Regards

CCR

Carson Penticuff · 02/15/24

You can wrap that function in NOT() to reverse the logic:

=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, CONTAINS("Quick Win", @cell), AssignedText:AssignedText, CONTAINS("First.Last1", @cell), StatusText:StatusText, NOT(CONTAINS("Complete", @cell)))

UserNewQuestions · 02/15/24

Carson;

That worked perfectly! Thank you.

Question is there a document that provides additional assistance for multifunctional usage?

Just trying to solve these on my own and I am finding that this type of info is rarefied.

Again thank you for solving this!!!

Regards

CCR

Carson Penticuff · 02/15/24

@UserNewQuestions

This community site is loaded with common questions and answers. You can learn a lot regarding best practices and common workarounds from browsing through the site. One tip I can give... instead of searching directly on this site, search google and the top results will almost always be links to this site. I tend to get better results that way.

Reading through the descriptions of all functions can be useful: