# Health Icon Formula Help

Hi All -

Longtime lurker, first time poster.

I am attempting to add a Health Column to a smartsheet created by a colleague.

I have been in the forums checking answers to similar, and tried to kluge my own formula together, but I've either missed the logic or I'm trying to do too much.

What i'm trying to achieve

rollup logic for parent/child

gray if there is no date assigned in either start date or final deadline OR % complete is 0 OR Status is "On Hold" or "Cancelled".

Green if % Complete is 100%,

Red if % Complete is less than 25% OR Final Deadline is Today.

Yellow if % Complete is less than 75% AND Final Deadline is within a week from today

formula text here:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", "Gray"))IF(TODAY() > [Final Deadline]@row, "Red", IF(TODAY(7) > = [Final Deadline]@row, IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 1, "Yellow", "Green")), IF(OR(ISBLANK([Final Deadline]@row), ISBLANK([% Complete]@row), (Status@row = "On Hold"), (Status@row = "Cancelled"), "Gray")))

Screenshot below

any advice on tweaks to make the formula work?

• ✭✭✭✭✭✭

Give this a try:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", IF(COUNTIFS(CHILDREN(), "Gray") > 0, "Gray", IF(OR([Final Deadline]@row = "", [% Complete]@row = "", Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25), "Red", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow", "Green")))))))

• ✭✭✭✭✭✭

Give this a try:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", IF(COUNTIFS(CHILDREN(), "Gray") > 0, "Gray", IF(OR([Final Deadline]@row = "", [% Complete]@row = "", Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25), "Red", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow", "Green")))))))

• Thank you @Paul Newcome

the formula worked better than my initial attempts, but I found a bit of weirdness related to dates/no dates and % complete. (this is an issue when taking over another's project and the schedule is incomplete.)

Screenshot below showing new formula in draft template. it's pulling gray into the health column even when the % complete is above 0 (which should be not gray) and when the % complete is 100% (complete) which should be green.

can you assist?

• ✭✭✭✭✭✭

That is coming from this part of your original post:

"gray if there is no date assigned in either start date or final deadline OR % complete is 0 OR Status is "On Hold" or "Cancelled"."

• ah, okay. let me play around a bit more with the logic and return with clarity in the request.
I appreciate you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!