AND(
NOT(
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" &
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" &
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" &
"WA:WV:WI:WY:PR", State)),
NOT(
OR(Country = "US", Country = "USA", Country = "United States", ISBLANK(Country))))
When I change AND( to OR(, it works the way I want it to, but I just don't get why it doesn't for AND(.
When I, for example, put State as "AA", which is not in CONTAINS(, it works with "US", which is a part of OR(. Am I missing something here? Why is the formula not equating to TRUE with State = AA and Country = US?
7 answers
Perhaps formatting it right would help.
OR(
AND(
LEN(State) <> 2,
NOT(
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" &
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" &
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" &
"WA:WV:WI:WY:PR", State)
)
),
NOT(
OR(
Country = "US",
Country = "USA",
Country = "United States",
ISBLANK(Country)
)
)
)
Let's examine what this rule actually says:
Length of State <> 2
AND
The state is not in any states in the list
This condition is incorrect because if the length is not 2, then by definition it will not be any value from this list. I'd say the length check is irrelevant because all the states you are checking have a 2-character length anyway.
It shouldn't be something that would make the rule not work, it's just an irrelevant statement to add. The result would be the same whether you had it or not. Are you saying that this rule does not work at all like this?
