Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Terry_0101Terry_0101 

How to use OR in this formula?

How to have this read "if the state contains certain states OR if it contains Canada, then tag it as US - West"?

IF(ISBLANK( State ), "", 
IF(ISBLANK( Country ), "", 
IF(CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada", State ), "US - WEST",  
IF(CONTAINS("Canada", Country ), "US - WEST", ""))))
Best Answer chosen by Terry_0101
Jason Curtis NBSFDGJason Curtis NBSFDG
Part of what is happening is as soon as one of the first if's evaluates as true then it isn't looking at the other ifs, so if State was blank but country was Canada it will never check for the country because it already returned a true based on the blank state.

You could do this (tested to work):

IF(AND(ISBLANK(ShippingState),ISBLANK(ShippingCountry)), "", 
IF(CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada",  ShippingState  ), "US - WEST",  
IF(CONTAINS("Canada", ShippingCountry ), "US - WEST", "")))




 

All Answers

Jason Curtis NBSFDGJason Curtis NBSFDG
Part of what is happening is as soon as one of the first if's evaluates as true then it isn't looking at the other ifs, so if State was blank but country was Canada it will never check for the country because it already returned a true based on the blank state.

You could do this (tested to work):

IF(AND(ISBLANK(ShippingState),ISBLANK(ShippingCountry)), "", 
IF(CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada",  ShippingState  ), "US - WEST",  
IF(CONTAINS("Canada", ShippingCountry ), "US - WEST", "")))




 
This was selected as the best answer
Jason Curtis NBSFDGJason Curtis NBSFDG
I've got to retract my earlier formula, seems there is some funkiness around the contains statement. I think you'd be much better off using the case statemment with an IF. One thing to make sure though is that you convert to upper case, the CASE statement is sensitive.

Try this:

IF(UPPER(ShippingCountry) = "Canada", "US - WEST",
CASE( UPPER(ShippingState),
"ALASKA", "US - WEST", 
"CALIFORNIA", "US - WEST",
"OREGON", "US - WEST",
"WASHINGTON", "US - WEST",
"HAWAII", "US - WEST",
"NEVADA", "US - WEST",
""))
Jason Curtis NBSFDGJason Curtis NBSFDG
The other challenge around contains is you might get false positives when you have a small word that is a subset of a bigger word that you are realling checking against.