Overall health of a project

Bob Kernan

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.

Bassam Khalil

@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:

Bassam Khalil

@Bob Kernan

Excellent, you are now Smartsheet expert:)

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.

Bob Kernan

Thank you Bassam! Here they are:

Bassam Khalil

@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.

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.

Bob Kernan

Just shared a copy with you. Thank you Bassam!

Bassam Khalil

@Bob Kernan

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

Bob Kernan

Bassam Khalil

@Bob Kernan 

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

Bassam Khalil

@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:

Bob Kernan

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

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?

Bob Kernan

Yes that is correct.

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.

Bassam Khalil

@Bob Kernan

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

Bob Kernan

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")))), "")