SharePoint List calculated field syntax

Question

I am unable to get this SIMPLE calculated field to work. Note that I am not a programmer and do not use codes a lot. So please help in details.

I have a SharePoint list where I want to get a simple calculated field based on 2 column selections (both have multiple choices).

I have a field with region/country choices and one with Roles.

So, if I select CANADA from my [REGION/COUNTRY] field, and then select CCC as a role in the column [Role] I want the result in that calculated field to be "1971".

So, you get that I have multiple possibilities to add in my formula Can you help on the recipe?

Answer 1

You can use If function in such cases which has syntax like:

IF(logical_test, value_if_true, value_if_false)

So, for your condition, it will be like:

=IF(AND([REGION/COUNTRY] = "CANADA", [Role] = "CCC"), "1971", "")

Similarly, you can add multiple nested IF conditions like:

=IF(AND([REGION/COUNTRY] = "CANADA", [Role] = "CCC"), "1971", IF(AND([REGION/COUNTRY] = "CANADA", [Role] = "AAA"), "1900", ""))

This will result in "1900" if you select "CANADA" in [REGION/COUNTRY] & "AAA" in [Role] field.

---

Refer below documentations for more information, syntax & available function you can use in calculated columns:

1. Calculated Field Formulas
2. Examples of common formulas in lists

---

Note:

1. Up to 7 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

2. Choice field with multiple selections allowed (checkboxes) is not supported to use in calculated column formula.

Answer 2

For example, you could use below formula in the calculated column.

=if(And([region/country]="Canada",[role]="ccc"),1971,if(And([region/country]="China",[role]="bbb"),1972,if(And([region/country]="America",[role]="aaa"),1973,1974)))

Result: