I have this formula for a custom sales region field Look-up. SFDC says "Error: Syntax error. Missing ')'" I can't find a problem. Can you help?

IF( OR( ISBLANK(BillingCountry), UPPER(BillingCountry) = 'UNITED STATES' , UPPER(BillingCountry) = 'USA', UPPER(BillingCountry) = 'US', UPPER(BillingCountry) = 'U.S.', UPPER(BillingCountry) = 'U.S.A.' ), 

IF ( OR( BillingState = 'CA' , BillingState = 'WA' , BillingState = 'OR' , BillingState = 'ID' , BillingState = 'NV' , BillingState = 'MT' , BillingState = 'WY' , BillingState = 'CO' , BillingState = 'UT' , BillingState = 'AZ' , BillingState = 'NM'), 
"1 - West" 
,
IF ( OR( BillingState = 'IL' , BillingState = 'ND' , BillingState = 'SD' , BillingState = 'MI' , BillingState = 'NE' , BillingState = 'IA' , BillingState = 'KS' , BillingState = 'OK' , BillingState = 'TX' , BillingState = 'IL' , BillingState = 'MN' , BillingState = 'OH' , BillingState = 'WI' , BillingState = 'IN' , BillingState = 'CANADA'), 
"2 - MIDWEST" 
, 
IF( OR( BillingState = 'NY' , BillingState = 'ME' , BillingState = 'NH' , BillingState = 'MA' , BillingState = 'RI' , BillingState = 'CT' , BillingState = 'NJ' , BillingState = 'DE' , BillingState = 'D.C.' , BillingState = 'VA' , BillingState = 'WVA' , BillingState = 'NC' , BillingState = 'SC' , BillingState = 'GA' , BillingState = 'FL' , BillingState = 'VT' , BillingState = 'PA' , BillingState = 'MD' , BillingState = 'KY' , BillingState = 'TN' , BillingState = 'AR' , BillingState = 'AL' , BillingState = 'MS' , BillingState = 'LS'), 
"3 - East" 
, 
"Unknown" ) )
, 
IF( LEN(BillingCountry) > 0, "4 - International", "Unknown") 
)

Attribution to: Sam

Possible Suggestion/Solution #1

Nearly there, you're just missing a closing brace after the 3-East value, so it should read

BillingState = 'MS' , BillingState = 'LS'), "3 - East" , "Unknown" ) ) ) /*a closing brace missing here */
 , IF( LEN(BillingCountry) > 0, "4 - International", "Unknown") )

Added that brace and it compiled okay.

Let me try to optimise this formula for you, if i can, but meanwhile this works after adding the missing closing brace

Here's a possibly optimised form which you might want to try :

IF( AND(
OR(
 ISBLANK(BillingCountry),
 CONTAINS('UNITED STATES|USA|US|U.S.|U.S.A.', UPPER(BillingCountry))  
 ) 
,NOT(ISBLANK(BillingState))),

IF (
CONTAINS( 'CA|WA|OR|ID|NV|MT|WY|CO|UT|AZ|NM', BillingState), "1 - West" , 
IF ( 
CONTAINS('IL|ND|SD|MI|NE|IA|KS|OK|TX|IL|MN|OH|WI|IN|CANADA', BillingState), "2 - MIDWEST" , 

IF( CONTAINS('NY|ME|NH|MA|RI|CT|NJ|DE|D.C.|VA|WVA|NC|SC|GA|FL|VT|PA|MD|KY|TN|AR|AL|MS|LS', BillingState), "3 - East" , 
"Unknown" ) )) ,  /*was missing closing braces*/

IF( NOT(ISBLANK(BillingCountry)), "4 - International", "Unknown") )

The only thing I noticed is if the BillingCountry is left blank in your original formula, it to evaluated "1 - West", I'm guessing you want it to be Unknown, I've fixed this in my optimised formula by checking for NOT(ISBLANK(BillingState)) to force it to go into the ELSE.

P.S. The only slightly risque' thing about CONTAINS is that it would match say CA in CANADA, however since the 1-West Branch occurs earlier, it evaluates okay. Thankfully there aren't any other State Abbreviations trapped inside CANADA ! Not in your list anyways, so it should be okay to use CONTAINS

Attribution to: techtrekker
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4013