On one side, there are four dropdown columns (Scope, Time, Resources and Budget) that can contain only one the following values: Green, Yellow and Red.

On the other side, I want to make "Project Health" a calculated column with those same options (green, yellow and red) based on the 4 columns above.

The potential cases would be the following:

  1. If one of the four columns is RED, "Project Health" is RED
  2. If one of the four columns is YELLOW, "Project Health" is YELLOW
  3. If all four columns are GREEN, "Project Health" is GREEN.

I am using SharePoint 365.

  • What if one column value is RED, 2nd column value is YELLOW and 3rd column value is GREEN? Feb 17, 2021 at 17:12
  • 1
    It would always take the "lowest", in this case RED. If there are no REDs, it would take YELLOW and if they are all GREEN, it would be green. The formula below worked for me.
    – Kastriot
    Feb 18, 2021 at 9:10

2 Answers 2


Use this formula:

=IF(OR([Scope]="RED", [Time]="RED", [Resources]="RED", [Budget]="RED"), "RED", IF(OR([Scope]="YELLOW", [Time]="YELLOW", [Resources]="YELLOW", [Budget]="YELLOW"), "YELLOW", IF(OR([Scope]="GREEN", [Time]="GREEN", [Resources]="GREEN", [Budget]="GREEN"), "GREEN", "")))

Microsoft documentations:

  1. Calculated Field Formulas
  2. OR function
  3. IF function


  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  • 1
    This did the job. Thanks a lot
    – Kastriot
    Feb 18, 2021 at 9:09

Please the following steps:

1.Create a Calculated column named "Project Health" and use the below formula:


enter image description here

2.Click on the "Project Health" field and select Column settings, and then select Format this column

enter image description here

3.On the Format column Panel, click on Advanced mode. Paste the following JSON formatting:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "debugMode": true,
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Project_x0020_Health] == 'RED', '#FA5858',if([$Project_x0020_Health] == 'GREEN', '#82FA58','#F4FA58'))",
    "font-size": "14px",
    "color": "#000000"

enter image description here

4.Save it

enter image description here

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.