RGY Status - Overall Roll up

lindsey.jones291441

Hello helpful Smartsheet-ers!

I have several rows that are utilizing the RGY buttons for health. I am trying to roll these up to a general status based on the choices in the individual rows. Can anyone help with a single formula that would capture:

Green if all elements (rows) are green

Yellow if one or more elements are yellow

Red if one or more elements are red.

So at the end of the day, I have 10 rows, 2 of those rows are red, I want the general status to show red; or all 10 rows are green, I want the general status to show red, and so on... 

Does that make sense?

 

Thanks so much, Lindsey

Andrée Starå

Hi Lindsey,

Are you using parents and children or how are the sheet setup? 

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

lindsey.jones291441

Hi Andree - you actually helped me with the first part of this (THANK YOU!). The original GYR Health is generated based off of due date and Percent of Completion. Because we have several different hospital markets completing their own sheet, I want to roll up a general status that I'll pull into a dashboard in a place where we can see how all hospital markets are generally performing against goals.

Does that help explain?

Andrée Starå

Happy to help and yes that helps!

Try something like this.

Place the formula below in the Parent of the Health column.

You might need to change the order and the range if you don't use parent/children.

=IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; "Yellow"))

The same version but with the below changes for your and others convenience.

=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

lindsey.jones291441

It didn't quite work - I'm playing with what you sent and seeing what I can figure out. 

Andrée Starå

Did you get an error message?

Best,

Andrée