0

I am trying to assign numerical values based on a list of countries on a SharePoint List (SharePoint in Microsoft 365). I want to autofill a column with numbers based on another column. I have no problem doing this up to 20 if statements. Past that, I get an error.

This is what I have:

=IF(Country="Australia",7,IF(Country="Austria",8,IF(Country="Belgium",13,IF(Country="Canada",26,IF(Country="Czechia",41,IF(Country="Denmark",43,IF(Country="Estonia",51,IF(Country="European Commission",54,IF(Country="Finland",55,IF(Country="France",56,IF(Country="Germany",60,IF(Country="Greece",63,IF(Country="Hungary",68,IF(Country="Ireland",74,IF(Country="Other",999,IF(Country="United States",163,IF(Country="United Kingdom",162,IF(Country="Türkiye",157,IF(Country="The Netherlands",153,IF(Country="Switzerland",148))))))))))))))))))))

However if I add another IF statement i.e. IF(Country="Sweden",147 and add the tail ), I get an error.

Any advice?

2
  • wouldn't it be easier to use a Lookup column?
    – teylyn
    Commented Jul 1 at 20:45
  • I’m checking how the things are going on about this issue. Whether the answer helps you?
    – Xyza_MSFT
    Commented Jul 2 at 6:33

1 Answer 1

1

This is a known limitation with SharePoint calculated column formulas.

You can use maximum 19 nested IF functions in calculated column formula.

If you have more than 19 nested IF functions/conditions, you can concatenate those using & in the formula.

Example:

IF(State="AL","Alabama",
IF(State="AK","Alaska",
IF(State="AZ","Arizona",
IF(State="AR","Arkansas",
IF(State="CA","California",
IF(State="CO","Colorado",
IF(State="CT","Connecticut",
IF(State="DE","Delaware",
IF(State="FL","Florida",
IF(State="GA","Georgia",
IF(State="HI","Hawaii",
IF(State="ID","Idaho",
IF(State="IL","Illinois",
IF(State="IN","Indiana",
IF(State="IA","Iowa",
IF(State="KS","Kansas",
IF(State="KY","Kentucky",
IF(State="LA","Louisiana",
IF(State="ME","Maine",""))))))))))))))))))) &
IF(State="MD","Maryland",
IF(State="MA","Massachusetts",
IF(State="MI","Michigan",
IF(State="MN","Minnesota",
IF(State="MS","Mississippi",
IF(State="MO","Missouri",
IF(State="MT","Montana",
IF(State="NE","Nebraska",
IF(State="NV","Nevada",
IF(State="NH","New Hampshire",
IF(State="NJ","New Jersey",
IF(State="NM","New Mexico",
IF(State="NY","New York",
IF(State="NC","North Carolina",
IF(State="ND","North Dakota",
IF(State="OH","Ohio",
IF(State="OK","Oklahoma",
IF(State="OR","Oregon",
IF(State="PA","Pennsylvania",""))))))))))))))))))) &
IF(State="RI","Rhode Island",
IF(State="SC","South Carolina",
IF(State="SD","South Dakota",
IF(State="TN","Tennessee",
IF(State="TX","Texas",
IF(State="UT","Utah",
IF(State="VT","Vermont",
IF(State="VA","Virginia",
IF(State="WA","Washington",
IF(State="WV","West Virginia",
IF(State="WI","Wisconsin","")))))))))))

References:

  1. What is the workaround for limit in nested if statements in sharepoint calculated column? (above example is from this reference)
  2. SharePoint List - Multiple IF AND function in calculated column

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.