SharePoint Online – Using multiple if statements within a calculated column

The syntax for the formula of a nested IF statement within a calculated field is not that obvious as there is no ELSE command within the statement like most other programming languages . However, it does follow a logic and like with some other SharePoint related logic and programming quirks it’s slightly different that the norm.

= IF ([Column]=value1,
“true value”,
IF([Column]=value2,
“elseif value”,
“elseif else value”
)
)

So for example, if the column is Impact and the return value should following the following logic;

  • If the Impact is zero then return “green”
  • or if the Impact is less than 30 but not zero then return “yellow”
  • or if the Impact is less than 60 “Medium” but greater than 30 then return “orange”
  • and finally any impact over 60 return “red”

Then the calculated column would have the formula;

=IF([Impact]>60,
“red”,
IF([Impact]>30,
“orange”,
IF([Impact]=0,
“green”,
“yellow”
)
)
)

Beware there is a limit on the nested IF statements you can embed within the formula this is currently set at 19. If you need more than this then return an empty string on the last IF statement and concatenate (using the & operator) another 19 IF statements and so on.

About the author