Formula to return value based on multi options in another cell

Sophie C

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

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

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

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!