Rollup Health

rachkh

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!

Christian G.

=IF(COUNTIFS(CHILDREN(), "Red") >= 3, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") >= 5, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))

rachkh

@ChristianCo. Thanks for responding! However when I use that formula it returns blank. No red, yellow, or green. What do you think?

Paul Newcome

You will need to be more specific in your criteria. What is supposed to happen if there are less than 3 reds or less than 5 yellows but they are not all green?

rachkh

@Paul Newcome

What about this?

If less than 3 reds, but I have 5 or more yellows, turn yellow

If less than 3 reds but 5 or less yellows, turn green

If more than 3 reds and 5 or more yellow, turn red (red trumps yellow)

If more than 3 reds and 5 or less yellow, turn red

Everything else turn green. So ...

If 5 or more tasks are yellow turn “yellow”

If 3 or more tasks are red turn “red” 

Christian G.

=IF(COUNTIFS(CHILDREN(), "Red") >= 3, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") >= 5, "Yellow", "Green"))

" Everything else turn green"