# RGY Status - Overall Roll up

edited 12/09/19

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

• ✭✭✭✭✭✭

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, [email protected])

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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?

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

• ✭✭✭✭✭✭

Did you get an error message?

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!