SharePoint Online – Using multiple if statements within a calculated column

Kulvinder Singh | 1st May 2020 | Business Productivity

The syntax for the formula in SharePoint Online 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 in SharePoint Online 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.

Contact risual today to ensure that your business challenges are overcome or follow us on Twitter for the latest risual updates.  

About the author

Kulvinder Singh