Formula to return value based on multi options in another cell

Options

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

Tags:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    A Rose ✭✭✭✭✭
    edited 02/14/23
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!