Overall health of a project

I have a sheet that tracks multiple projects. Each project has multiple tasks. Each task has a health column. I would like to create an "overall health" column that looks at the others and assigns the most critical health. In this case the most critical would be Red (overdue), followed by Yellow (warning), then Green (on track) and finally Blue (complete).

See examples below.

In this example, "In-person BF Health" is red so overall health would be red since that is the most critical issue to address.


Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Bob Kernan

    The following is the Overall Health formula:

    =IFERROR(IF(COUNTIF([HQY Live Webinar Deck Deliverables Health]@row:[Custom Flyer Health]@row, "Red") >= 1, "Red", IF(COUNTIF([HQY Live Webinar Deck Deliverables Health]@row:[Custom Flyer Health]@row, "Yellow") >= 1, "Yellow", IF(COUNTIF([HQY Live Webinar Deck Deliverables Health]@row:[Custom Flyer Health]@row, "Blue") = 8, "Blue", IF(COUNTIF([HQY Live Webinar Deck Deliverables Health]@row:[Custom Flyer Health]@row, "Green") = 8, "Green")))), "")

    the following screenshot shows the result:



    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Bob Kernan

    Excellent, you are now Smartsheet expert:)

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!