function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
moringdmoringd 

Need Help With An Advanced Region Formula

I need a region formula based off states and countries, but for New York and New Jersey it is also based of the account name that starts with #-J and K-Z?  Here is what im working with, but i know its not right.The bold area is what needs to be fixed. Any help would be amazing.

 

IF(LEN(BillingState)=0, "None",
IF(CONTAINS("WA:OR:CA:AZ:NM:TX:HI:AK", BillingState), "West",
IF(CONTAINS("ID:NV:UT:MT:WY:CO:ND:SD:NE:KS:OK:MN:IA:MO:AR", BillingState), "Central",
IF(CONTAINS("LA:TN:MS:AL:FL:GA:SC:NC:VA:WV:DC:MD:DE:PA", BillingState), "Southeast",
IF(CONTAINS("PR", BillingCountry), "Great Lakes",
IF(CONTAINS("WI:IL:MI:IN:KY:OH", BillingState), "Great Lakes",
IF(CONTAINS("NY:NJ", BillingState), "East",
IF( BEGINS(Name, "A, B, C, D, E, F, G, H, I, J"), "East 1",
IF( BEGINS(Name, "K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z"), "East 2",
IF(CONTAINS("AD:AL:AT:BA:BE:BG:BY:CH:CZ:DE:DK:EE:ES:FI:FO:FR:FX:GB:GI:GR:HR:HU:IE:IS:IT:LI:LT:LU:LV:MC:MD:MK:MT:NL:NO:PL:PT:RO:SE:SI:SJ:SK:SM:UA:VA:CS:UK", BillingCountry), "East",
IF(CONTAINS("CA:JM", BillingCountry), "Central",
IF(CONTAINS("CT:RI:MA:NH:VT:ME", BillingState), "New England", "Other"))))))))))))

MohandaasMohandaas

Moring,

 

You just have to combine the conditions to check both Billing state and name.

 

IF(AND(CONTAINS("NY:NJ", BillingState),BEGINS(Name, "A, B, C, D, E, F, G, H, I, J")), "East1",)

IF(AND(CONTAINS("NY:NJ", BillingState),BEGINS(Name, "K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z")), "East2",)

 

Hope this helps...