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
-
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.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"
-
Excellent, you are now Smartsheet expert:)
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"
Answers
-
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.
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:
-
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.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"
-
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.
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!
-
Ok Bob I will check it, please sray with me for any questions.
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"
-
I created the formula for you in the shared sheet, please check it if this is what you need.
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"
-
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.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"
-
my green and blue indictors aren't showing up when selected individually.
-
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?
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.
-
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.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"
-
But if you want it in this configuration I will do it for you.
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!