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.

@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 Excellent, you are now Smartsheet expert:)

Overall health of a project

Answers

Bassam Khalil ✭✭✭✭✭✭

07/24/21 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 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Checking multiple sheets for duplicates
Hi, I have a workspace that is being used for uploading new information by a group of techs. This information is checked and then uploaded a database by another team. Currently, I have the sheet working to check duplicates within one sheet, but once the data is uploaded to the database the line is moved to another…
Can you create email notifications on several different cells in a single column
Good morning, My project team asked me to create an inventory smartsheet. They are requesting email notifications for a single column, but for different inventory amounts on each cell. Is this possible??
Using COUNTIFS with CONTAINS as a Criterion
I have a multi select list column in Sheet 1, containing campuses. In sheet two I am trying to get a count of how many times each campus is listed in the campus column in sheet 1. Here is my current formula: =COUNTIFS({Event Campus}, CONTAINS("Cypress", {Event Campus})) The results are always "0" for each campus. Given…

Overall health of a project

Answers

Help Article Resources

Categories