SharePoint IF(OR formula

Question

I have a column (Country) in Sharepoint with the name of countries. I want to create another calculated column that separates the Countries by regions as follows:

Latin American countries received the value "LAA" Countries in North America received the value "NAA" Countries in Europe received the "EMEA" value And countries in Asia received the value "APAC"

I created the following formula for this:

=IF(OR([Country]="Argentina",[Country]="Bolivia",[Country]="Brazil",[Country]="Chile",[Country]="Colombia",[Country]="Costa Rica",[Country]="Cuba",[Country]="Dominican Republic",[Country]="Ecuador",[Country]="El Salvador",[Country]="Guatemala",[Country]="Haiti",[Country]="Honduras",[Country]="Jamaica",[Country]="Mexico",[Country]="Nicaragua",[Country]="Panama",[Country]="Paraguay",[Country]="Peru",[Country]="Puerto Rico",[Country]="Trinidad and Tobago",[Country]="Uruguay",[Country]="Venezuela"),"LAA",IF(OR([Country]="Canada",[Country]="United States"),"NAA",IF(OR([Country]="Austria",[Country]="Belgium",[Country]="Bulgaria",[Country]="Croatia",[Country]="Cyprus",[Country]="Czech Republic",[Country]="Denmark",[Country]="Estonia",[Country]="Finland",[Country]="France",[Country]="Germany",[Country]="Greece",[Country]="Hungary",[Country]="Iceland",[Country]="Ireland",[Country]="Italy",[Country]="Latvia",[Country]="Liechtenstein",[Country]="Lithuania",[Country]="Luxembourg",[Country]="Malta",[Country]="Monaco",[Country]="Netherlands",[Country]="Norway",[Country]="Poland",[Country]="Portugal",[Country]="Romania",[Country]="Slovakia",[Country]="Slovenia",[Country]="Spain",[Country]="Sweden",[Country]="Switzerland",[Country]="United Kingdom"),"EMEA",IF(OR([Country]="Afghanistan",[Country]="Bangladesh",[Country]="Bhutan",[Country]="Brunei",[Country]="Cambodia",[Country]="China",[Country]="India",[Country]="Indonesia",[Country]="Japan",[Country]="Kazakhstan",[Country]="Kyrgyzstan",[Country]="Laos",[Country]="Malaysia",[Country]="Maldives",[Country]="Mongolia",[Country]="Myanmar",[Country]="Nepal",[Country]="North Korea",[Country]="Pakistan",[Country]="Philippines",[Country]="Singapore",[Country]="South Korea",[Country]="Sri Lanka",[Country]="Taiwan",[Country]="Tajikistan",[Country]="Thailand",[Country]="Turkmenistan",[Country]="Uzbekistan",[Country]="Vietnam"),"APAC","Region undefined")

However, this formula has a syntax error. Can you help me to correct it please?

thebernardlim · Answer 1 · 2023-04-28T01:55:22.340

A couple of things that were wrong with the formula you posted:

Missing closing brackets
Too many OR conditions. An OR only accepts a maximum of 30 conditions. This is the reason you will see 2 EMEAS and APAC IF conditions below. Refer to this link for more info

Another tip I would give when handling huge nested IF code will be to indent them in an editor which will make it much easier to read and also spot errors. Also to debug, reduce the number of OR conditions first and slowly work your way up.

 =IF(OR([Country]="Argentina",[Country]="Bolivia",[Country]="Brazil",[Country]="Chile",[Country]="Colombia",[Country]="Costa Rica",[Country]="Cuba",[Country]="Dominican Republic",[Country]="Ecuador",[Country]="El Salvador",[Country]="Guatemala",[Country]="Haiti",[Country]="Honduras",[Country]="Jamaica",[Country]="Mexico",[Country]="Nicaragua",[Country]="Panama",[Country]="Paraguay",[Country]="Peru",[Country]="Puerto Rico",[Country]="Trinidad and Tobago",[Country]="Uruguay",[Country]="Venezuela")
    , "LAA"
    , IF(OR([Country]="Canada",[Country]="United States")
        ,"NAA"
        ,IF(OR([Country]="Austria",[Country]="Belgium",[Country]="Bulgaria",[Country]="Croatia",[Country]="Cyprus",[Country]="Czech Republic",[Country]="Denmark",[Country]="Estonia",[Country]="Finland",[Country]="France",[Country]="Germany",[Country]="Greece",[Country]="Hungary",[Country]="Iceland",[Country]="Ireland",[Country]="Italy",[Country]="Latvia",[Country]="Liechtenstein",[Country]="Lithuania",[Country]="Luxembourg",[Country]="Malta",[Country]="Monaco",[Country]="Netherlands",[Country]="Norway",[Country]="Poland",[Country]="Portugal",[Country]="Romania",[Country]="Slovakia",[Country]="Slovenia",[Country]="Spain")
            , "EMEA"
            , IF(OR([Country]="Sweden",[Country]="Switzerland",[Country]="United Kingdom")
                , "EMEA"
                , IF(OR([Country]="Afghanistan",[Country]="Bangladesh",[Country]="Bhutan",[Country]="Brunei",[Country]="Cambodia",[Country]="China",[Country]="India",[Country]="Indonesia",[Country]="Japan",[Country]="Kazakhstan",[Country]="Kyrgyzstan",[Country]="Laos",[Country]="Malaysia",[Country]="Maldives",[Country]="Mongolia",[Country]="Myanmar",[Country]="Nepal")
                    , "APAC"
                    , IF(OR([Country]="North Korea",[Country]="Pakistan",[Country]="Philippines",[Country]="Singapore",[Country]="South Korea",[Country]="Sri Lanka",[Country]="Taiwan",[Country]="Tajikistan",[Country]="Thailand",[Country]="Turkmenistan",[Country]="Uzbekistan",[Country]="Vietnam")
                        , "APAC"
                        , "Region Undefined"
                    )
                )
            )
        )
    )
  )

Hope this helps!

Thanks for the answer! I tried the way you suggested but it still throws a syntax error. Would you have any other suggestions? — Felipe André, Apr 27 '23 at 19:15
@FelipeAndré I revised my answer. Please try again. If it works, please also help Accept this as an answer. Thanks. — thebernardlim, Apr 28 '23 at 01:55
Great. Can you also accept the answer (tick), so it can help others with similar problems? Thanks — thebernardlim, May 04 '23 at 01:24

SharePoint IF(OR formula

1 Answers1