IF formula with AND OR nested

Options

Hello,

I'm attempting to create a formula that will determine if one or both options have been selected in a dropdown and assign that a numerical value.

What I have now is =IF(OR([Communications provider]@row = "Provider A", [Communications provider]@row = "Provider B", AND([Communications provider]@row = "Provider A", [Communications provider]@row = "Provider B")), 0, 1)

There are about 6 options in the dropdown, and I want to assign a 1 to anything other than Provider A and/or Provider B.

Thank you for the help!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 09/21/22 Answer ✓
    Options

    Hey @ssuddendorf

    Try this

    IF(OR(NOT(CONTAINS("Provider A", [Communications provider]@row)), NOT(CONTAINS("Provider B", [Communication provider]@row))), 1)

    I chose the CONTAINS function to help in a multi select field. I also could have used the HAS function. I did not bother to include the zero false option in this case because it is the same thing as doing nothing - it will leave a blank cell.

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 09/21/22 Answer ✓
    Options

    Hey @ssuddendorf

    Try this

    IF(OR(NOT(CONTAINS("Provider A", [Communications provider]@row)), NOT(CONTAINS("Provider B", [Communication provider]@row))), 1)

    I chose the CONTAINS function to help in a multi select field. I also could have used the HAS function. I did not bother to include the zero false option in this case because it is the same thing as doing nothing - it will leave a blank cell.

    Does this work for you?

    Kelly

  • ssuddendorf
    Options

    @Kelly Morgan this worked perfect thank you!

  • ssuddendorf
    Options

    @Kelly Moore I ran into a snag. Your formula give me a 0 if both Provider A and B are selected. But I need it to give me a 0 if only provider A or Provider B is selected.

    Option 1 - Provider A = 0

    Option 2 - Provider B = 0

    Option 3 - Provider A & Provider B = 0

    Option 4 - Provider C = 1

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @ssuddendorf

    I am a bit confused as it seems Option 3 exactly describes the scenario of when both A and B are selected. However, to get only the OR option of A OR B, try this

    =IF(COUNTIFS([Communications provider]:[Communications provider],OR(CONTAINS("Provider A", [Communications provider]@row), CONTAINS("Provider B", [Communication provider]@row)))<>1,1)

    Does this work for you

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!