Create Overall Health Cell from Health Column

Theresa Bruns

I created a Health Column using the following formula: =IF(AND(Status@row = "In progress", [Due Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Due Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "Complete", [Due Date]@row = TODAY()), "Blue", IF(AND(Status@row = "Complete", [Due Date]@row < TODAY()), "Blue", IF(AND(Status@row = "Complete", [Due Date]@row > TODAY()), "Blue", IF(AND(Status@row = "In progress", [Due Date]@row > TODAY()), "Green", IF(AND(Status@row = "In progress", [Due Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "Not started", [Due Date]@row > TODAY()), "Yellow")))))))))

With this formula, each section of the sheet has a Health status, so 17 out of 87 rows have a status color. In the column to the left, I would like to create an Overall Health of the project, but I haven't been successful finding or writing a formula. I hope you can help. Here is how I would like to define the overall health:

If all blue, the overall health cell is blue

If all green, the overall health cell is green

If all yellow, the overall health cell is yellow

If all red, the overall health cell is red.

If there is 1 red, the overall health is red.

If there is 1 yellow and no red, the overall health is yellow

If there is 1 green and no yellow or red, the overall health is green

Thank you for this forum, I have been able to develop this project through reading responses.

Mike Wilday

I would approach this with Count comparisons. Does this work for you? Just replace the Symbol Column Title with your actual symbol column name.

=IF(Countif([Symbol Column Title]:[Symbol Column Title], "Blue") = Count([Symbol Column Title]:[Symbol Column Title]), "Blue", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Red") = Count([Symbol Column Title]:[Symbol Column Title]), "Red", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Yellow") = Count([Symbol Column Title]:[Symbol Column Title]), "Yellow", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Green") = Count([Symbol Column Title]:[Symbol Column Title]), "Green", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Red") > 1, "Red", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Yellow") > 1, "Yellow", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Green") > 1, "Green",

Mike Wilday

I would approach this with Count comparisons. Does this work for you? Just replace the Symbol Column Title with your actual symbol column name.

=IF(Countif([Symbol Column Title]:[Symbol Column Title], "Blue") = Count([Symbol Column Title]:[Symbol Column Title]), "Blue", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Red") = Count([Symbol Column Title]:[Symbol Column Title]), "Red", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Yellow") = Count([Symbol Column Title]:[Symbol Column Title]), "Yellow", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Green") = Count([Symbol Column Title]:[Symbol Column Title]), "Green", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Red") > 1, "Red", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Yellow") > 1, "Yellow", IF(Countif([Symbol Column Title]:[Symbol Column Title], "Green") > 1, "Green",

Theresa Bruns

Thank you!!! That worked. I did add "equal to" after the greater than and put in the closing parentheses at the end and it works perfectly. I appreciate the help.

Mike Wilday

Whoops. I totally left those out! Working too quickly! Glad i could get it working for you.

Paul Newcome

I think we could simplify this a bit and just go with the last three criteria. If there is one "color", "color". That would also cover if ALL are the same color, so including those arguments becomes redundant. We can also start with red and work our way up to blue.

I would still use the count comparison as @Mike Wilday suggested though.

=IF(COUNTIFS([Color Column]:[Color Column], "Red") > 0, "Red", IF(COUNTIFS([Color Column]:[Color Column], "Yellow") > 0, "Yellow", IF(COUNTIFS([Color Column]:[Color Column], "Green") > 0, "Green", "Blue")))

This says if there is at least 1 red, then red (also covers if all are red). If no red, if there is at least one yellow, then yellow (also covers all yellow). If no red or yellow, if there is at least one green, then green (also covers all green). If no red, yellow, or green, then all must be blue, so blue.

Mike Wilday

@Paul Newcome I knew I was overcomplicating it. 🤣 But I didn't have time to fully contemplate it! 🤔

Paul Newcome

@Mike Wilday Haha. There have been PLENTY of times where I just dove in either correcting a formula or providing an exact formula without taking a step back to see if maybe there was a different approach that would work. Sometimes it just takes a second set of eyes. 👀

Theresa Bruns

Thanks! So happy to see this working!

Paul Newcome

@Profe17 Happy to help. 👍️

What initially got me reevaluating the formula as a whole was the fact that there are (for example) two sets of criteria that output "red". I had started to group those into OR statements for organization, so when I was then able to compare the two criteria for red side by side it made it easier to see that we could do away with the one criteria (all of them red) entirely. So the end result was something that I just happened to stumble upon when I was trying to organize it. Gotta love when those kinds of unintended results pop up. Haha

Mike Wilday

@Paul Newcome Thanks for your 👀's on it! 😁

Paul Newcome

@Mike Wilday Haha. Sure thing.