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

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

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

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

@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:

https://help.smartsheet.com/functions

And having the error messages page bookmarked for quick reference can assist in troubleshooting:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.