Need assistance with an extensive "If" statement which involves two separate columns

[JoshuaElphee]

I am utilizing "formula" to add a new column which will state "Y" or "N" based on logic:

if([award basis] = "Competitive" or "Open Competitive" and (!contains[Billing Method], "CP" or "TM" or "LH")
then "Y"
Else "N"
Endif

The logic is if award basis is either "Competitive or open competitive, and the field billing method does not contain "CP" "TM" or "LH", the new field should show "Y", otherwise "N".

This breaks after the "and" statement.

[ChrisTX]

Check the Functions (alteryx.com) page for syntax examples

Try this:

if [award basis] IN ("Competitive", "Open Competitive")  and !contains([Billing Method], "CP") and  !contains([Billing Method], "TM") and !contains([Billing Method], "LH")

[alexnajm]

if([award basis] = "Competitive" or [award basis]="Open Competitive") and (!contains([Billing Method], "CP") or !contains([Billing Method],"TM") or !contains([Billing Method],"LH")
then "Y"
Else "N"
Endif

[Raj]

@JoshuaElphee Just a bit of Structuring and your formula will be working.

IF([award basis] = "Competitive" OR [award basis]="Open Competitive")

And (!contains([Billing Method], "CP")
OR !contains([Billing Method],"TM")
OR !contains([Billing Method],"LH")
THEN "Y" Else "N"
Endif

[ChrisTX]

👆 This syntax is missing parenthesis around the multiple Contains functions, which may produce unexpected results when mixing AND and OR.