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 😊
Help Article Resources
Check out the Formula Handbook template!