Does NOT contain formula

damon.tackett

Hi everyone. I am trying to come up with a formula that provides a solution to this:

Column "Product" contains a multiselect dropdown with X, Y and Z as options.

Colum "Type" contains a dropdown list with multiple selections available, but needs to reflect "N/A" IF "Product" column as any combination of selections that does NOT include Y and/or Z.

So basically somthing like IF(product@row) NOT("X"), "N/A"

I know that isn't and actual formula but trying to express what I am looking for.

Thanks in advance to the great minds on there that understand formulas way better than I do!🙂

Paul Newcome

Try something along these lines...

=IF(NOT(CONTAINS("X", Product@row)), "N/A")

Paul Newcome

Try something along these lines...

=IF(NOT(CONTAINS("X", Product@row)), "N/A")

Sameer Karkhanis

Your combination mentioned in text and tentative formula are contradicting so just want to make sure you are looking to have a "N/A" value when Product column does not contain either Y or Z, correct? If yes then try the below,

=IF(NOT(OR(HAS(Product@row, "Y"), HAS(Product@row, "Z"))), "N/A")

damon.tackett

@Paul Newcome that was too easy! LOL Thank you.

brings up another question though....is there a way to have the formula allow a drop down selection if Product@row DOES contain "X" or maybe add N/A to the the drop down list and make the previous formula select "N/A" rather than just write it?

damon.tackett

@Sameer Karkhanis i think i talked myself into a circle. Ultimately the formula concept I provided was the more correct question. I just wanted to make sure that if Product@row included from the drop down "X, Y" or "X, Z" that it would not result in "N/A"

Sameer Karkhanis

Ok, in that case try this (In case you want some value to be displayed when the check does not result in N/A then you can add that into the formula below as part of else condition)

=IF(COUNTM(Product@row)>1, 
    IF(HAS(Product@row,"X"), 
        IF(NOT(OR(HAS(Product@row, "Y"), HAS(Product@row, "Z"))), "N/A")))

Paul Newcome

@damon.tackett

To output something different if it does contain "X", then you would leverage the third portion of the IF statement.

=IF(NOT(CONTAINS("X", Product@row)), "N/A", this is what happens if it does contain x)