Create Overall Health Cell from Health Column
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.
Best Answer

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

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

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.

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

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.

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

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

Thanks! So happy to see this working!

@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

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

Help Article Resources
Categories
Check out the Formula Handbook template!