Formula to return value based on multi options in another cell

Hey SS community,

Hoping someone can support on this one. I've been searching and searching and not been able to find the answer as of yet!

I'm working on a sheet that contains answers to an assessment. The answers are captured form an external form (ms forms) and added to Smartsheet via Powerautomate.

One of the questions is a multi select answer which adds the data to the cell like this:

In the next column, i'm hoping to add a value of '1' if the above highlighted content is present. Then if only one of the options is present, it should return a value of '0'.

The following formula has been provided to me by a SS support member but this also isn't working:

=IF(["When handling sharp objects","When assembling various products in manufacturing"], "1"), IF(["When handling sharp objects", "0"), IF(["When assembling various products in manufacturing", "0")

I believe the square brackets and additional quotation marks are causing the issue, so also tried a helper column to try and pull just the value through, but that also returned an 'invalid value' response.

I'm officially stuck! Someone help... please 😊

Thanks

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@Sophie C

=IF(AND(CONTAINS("When handling sharp objects", [When should you wear gloves?]@row), CONTAINS("When assembling various products in manufacturing", [When should you wear gloves?]@row)), 1, IF(AND(CONTAINS("When handling sharp objects", [When should you wear gloves?]@row), NOT(CONTAINS("When assembling various products in manufacturing", [When should you wear gloves?]@row))), 0, IF(AND(CONTAINS("When assembling various products in manufacturing", [When should you wear gloves?]@row), NOT(CONTAINS("When handling sharp objects", [When should you wear gloves?]@row))), 0)))

All comments

Mike TV

@Sophie C

A. Rose

Hi,

To check if it contains both variations, add a new column to the sheet (or use existing), use the below formula, right click to convert to column formula.

=IF(AND(CONTAINS("When assembling various products in manufacturing", [When should you wear gloves]@row), CONTAINS("When handling sharp objects", [When should you wear gloves]@row)), "1", "0")

The below will give you similar result, but only works if it's a multi select dropdown.

=IF(AND(HAS([When should you wear gloves]@row, "When assembling various products in manufacturing"), HAS([When should you wear gloves]@row, "When handling sharp objects")), "1", "0")

Sophie C

@Mike TV

Thank you! this has worked like a dream! I didn't think to include 'NOT CONTAINS' as part of the formula but makes perfect sense. Thanks again!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.