SUMIFS and CONTAINS with column criteria, Within CONTAINS can a "Not Equal To" operator be used.
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
Answers
-
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)))
-
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
-
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:
And having the error messages page bookmarked for quick reference can assist in troubleshooting:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!