IF formula with AND OR nested

ssuddendorf

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!

Kelly Moore

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

Kelly Moore

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

@Kelly Morgan this worked perfect thank you!

ssuddendorf

@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

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