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

    bassam.khalil2009@gmail.com

    ☑️ 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

    bassam.khalil2009@gmail.com

    ☑️ 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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Bob Kernan

    Hope you are fine, please write in this post the exact names of your columns that you are using in your sheet. to help me to create the exact formula for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Thank you Bassam! Here they are:


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/23/21

    @Bob Kernan

    I mean write the columns name as a text here because I don't want to open an image and try to writer it again. To avoid any spelling mistakes.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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 ✭✭✭✭✭✭

    @Bob Kernan

    Or if you like share me as an admin on a copy of your sheet after removing any sensitive data and I will write the exact formula for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Just shared a copy with you. Thank you Bassam!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Bob Kernan

    Ok Bob I will check it, please sray with me for any questions.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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 ✭✭✭✭✭✭

    @Bob Kernan 

    I created the formula for you in the shared sheet, please check it if this is what you need.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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

    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

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Bob Kernan
    Bob Kernan ✭✭
    edited 07/24/21

    my green and blue indictors aren't showing up when selected individually.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Bob Kernan

    You mean if the row contains 1 Green ball at least without any red or yellow one then the Overall Health will be Green and same thing for Blue?

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Yes that is correct.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Bob Kernan

    But this will give you wrong indicators about the overall health, because for sure after a while another column will be Red or yellow and will change the overall health to Red or yellow.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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 ✭✭✭✭✭✭

    @Bob Kernan

    But if you want it in this configuration I will do it for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Hi Bassam, I found this issue I think. I swapped the Green and Blue in the formula and removed the 8's. This appears to be working now. Thank you very much for your help!

    =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, "Green") >= 1, "Green", IF(COUNTIF([HQY Live Webinar Deck Deliverables Health]@row:[Custom Flyer Health]@row, "Blue") >= 1, "Blue")))), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!