Scheduled Health Rollup using "Red Yellow Green" formula
Question. I am using this formula for my individual task health:
=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))
Works great!
I would like to have a rollup / summary of my overall task health for the project. How would I do that formula?
I found this:
=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))
Would that work?
Best Answers

The metric you show is exactly why I mentioned losing the ability to use discretion when a Health rollup formula is in place. Many times folks don't want their rollup to be forced. Someone may have a yellow item or two that's just not important enough to cause the project to rollup to yellow.
As far as a better equation: I don't know of a better one myself. I would be using the one you posted, but would couple it with the automation otherwise the rollup will break once a row becomes a parent row. Unless of course you manually add the formula every time a row becomes a parent, but that manual effort and need to remember is not preferred or scalable.
Remember automation runs regularly (every couple minutes or so), so you won't see the formula appear instantly when a row becomes a parent.
BRgds,
Ray

Hi @rachkh
If you want it to look at all rows equally, you could use the DESCENDANTS Function instead of CHILDREN:
=IF(COUNTIFS(DESCENDANTS(), "Red") > 2, "Red", IF(COUNTIFS(DESCENDANTS(), "Yellow") > 4, "Yellow", IF(COUNTIFS(DESCENDANTS(), "Green") = COUNT(DESCENDANTS()), "Green", "Green")))
Or you could add the previous CHILDREN formula in to some of your other toplevel rows as well (e.g. the Grey ones).
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hi @rachkh,
Yes, that formula will allow for the Health rollup. This formula will have to be applied to each parent row's Health column.
Also, if you use a Level column or a Children Count column, you could use automation to enter your rollup formula into the Health column for each time a row becomes a parent row (and can remove it automatically when the row becomes no longer a parent row). You can't select "when a row becomes a parent" via the automation features, but you can use the Level column value to determine if the row is a parent row.
Just a side thought: The only thing this doesn't allow is for discretion to be used in reporting overall Health for a project. One tiny Yellow will cause the entire project to go Yellow. Often times these small Yellows aren't weighted enough for some people to want to change their project's overall Health.
Hope this helps!
BRgds,
Ray

Thanks @Ray Lindstrom!
I appreciate your explanation  is there a better equation that I should use? I have that metric to pull to my project dashboard. But as you said ... maybe there is a better way? Here is a snip of my dashboard:
Best,

The metric you show is exactly why I mentioned losing the ability to use discretion when a Health rollup formula is in place. Many times folks don't want their rollup to be forced. Someone may have a yellow item or two that's just not important enough to cause the project to rollup to yellow.
As far as a better equation: I don't know of a better one myself. I would be using the one you posted, but would couple it with the automation otherwise the rollup will break once a row becomes a parent row. Unless of course you manually add the formula every time a row becomes a parent, but that manual effort and need to remember is not preferred or scalable.
Remember automation runs regularly (every couple minutes or so), so you won't see the formula appear instantly when a row becomes a parent.
BRgds,
Ray

So ... on further thought. Is there a way to add to my existing rollup formula (see below):
=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))
that adds:
If 5 or more tasks are yellow turn “yellow”
If 3 or more tasks are red turn “red”
91% of my tasks are "Green" so when I only have one "red" and it turns the overall project “red” it isn’t a clear picture of health.
How would I edit my current formula to do that?
Any ideas?
Thanks!

Hi @rachkh
You can update what each COUNT is looking for. Right now you're simply looking for if the COUNT of each colour is "greater than 0" (so there's 1).
Instead, you can change this to look for a different count:
=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Green")))
Note that this means if you have 2 Red tasks and 3 Yellow tasks and only 1 Green task, the default at the end is Green. I may suggest having your default be Yellow, if not all of the children are Green:
=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow")))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. Thanks for commenting!! I appreciate any help I can get. So I tried your formula. I think something is off.
I tried this one, because my default is Green.
=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Green")))
It worked. But if you look at this screen shot. It is still returning "green" when you can see 7 "reds" shouldn't it return "red"
I'm using this for my individual tasks:
=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))
If that helps.

Hi @rachkh
Would you mind posting the screen capture again, showing what rows are Parent rows? (Which ones have the + or  symbol in the Primary Column).
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hopefully this is more helpful ;)

Hi @rachkh
Thank you, this definitely helps!
The CHILDREN function only looks at direct children of the current row.
For example in your image, there are only 2 Children for the Dark Blue Line: the 2 Grey Rows. These rows are Green and Yellow, therefore the Dark Blue row is Green.
Are you only placing the formula in that very top row? Or do you have it on each individual Parent row?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Well ... I feel dumb! I thought is was counting all the rows below. That makes total sense now.
Yes, I was only placing that formula in the top (dark blue) row as the rollup summary. Everything else I have been using:
=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))

Hi @rachkh
If you want it to look at all rows equally, you could use the DESCENDANTS Function instead of CHILDREN:
=IF(COUNTIFS(DESCENDANTS(), "Red") > 2, "Red", IF(COUNTIFS(DESCENDANTS(), "Yellow") > 4, "Yellow", IF(COUNTIFS(DESCENDANTS(), "Green") = COUNT(DESCENDANTS()), "Green", "Green")))
Or you could add the previous CHILDREN formula in to some of your other toplevel rows as well (e.g. the Grey ones).
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Help Article Resources
Categories
Check out the Formula Handbook template!