How to sum up column RGY statuses?

Carol Rykovanova

Hi Smartsheeters!

I am trying to sum up column RGY statuses. The formula that I use averages the sratus and I end up with either Green or Yellow. 

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

Please help me figure out where the error is.

I need to summarize status to show:

if all are Green, then Green

if at least one is Yellow, then Yellow

if at least one is Red, then Red

Thanks in advance,

Carol

Carol Rykovanova

Hey Kelly,

Thanks you very very much! I worked on this formula for a long time and lost sight of the obvious :)

Of course, you're right, as soon as I moved the rest of the rows to a level below - everything worked!

Thanks again,

Carol

Kelly Moore

Hi @Carol Rykovanova

I'm not sure which column you are trying to place your formula in. Without calling a specific columns, the CHILDREN() refers to the current column the formula is placed in. I called out the columns to be very clear to you what we were measuring.

=IF(COUNTIF(CHILDREN([your ryg ball]@row), "Green") = COUNT(CHILDREN([your primary column]@row)), "Green", IF(COUNTIF(CHILDREN([your ryg ball]@row), "Red") > 0, "Red", "Yellow"))

Please change my column names to match yours

Note that if you are placing the formula in the parent row of the same column where the RYG balls and the RYG balls of the Children change colors based on a formula (vs manual entry), we can help you set up a single formula for that column that would automatically know which formula to use. Let us know.

cheers,

Kelly

Carol Rykovanova

Hi Kelly!

Copy of Document (2).pdf

Thanks a lot for the answer, but it doesn't seem to solve my problem.

Please find an example document I'm working on attached.

I need to show the status of all tasks in row 1, Health column (sum of RYG statuses from rows 3, 7, 24-33), and, accordingly, in rows 3, 7, 24-33, Health column, the sum of RYG statuses from their Subtasks.

if all are Green, then Green

if at least one is Yellow, then Yellow

if at least one is Red, then Red

And I would be happy if you could help me set up a single formula for that column that would automatically know which formula to use.

Thanks again,

Carol

Kelly Moore

Hey Carol

It appears to me like the formula is producing what you described? For every parent, if all the children are green, green, if any reds then red, else yellow.

From the screenshot, this is how I interpret your sheet.

The overall parent is row3. It looks at row 7 and 24-32. Because row7 is red, Overall Parent3 is red

Sub Parent at row 7.

It gets it's status from children 8-23. Row 8 is red, so Sub Parent7 is red

Row 1 doesn't have any children so it behaves as an independent row.

Did I interpret your sheet as you intended it? To get Row1 involved then Row3 needs to be indented once. I believe that will then indent everything below it. You can check this by collapsing Row1. All other rows should then be hidden.

To differentiate between formulas of Parents and children, you can first check if the row is a parent using IF(COUNT(CHILDREN())>0, <insert your parent IF statements>, <insert non-parent row formula>.

Let me know how I can help.

Kelly

Carol Rykovanova

Hey Kelly,

Thanks you very very much! I worked on this formula for a long time and lost sight of the obvious :)

Of course, you're right, as soon as I moved the rest of the rows to a level below - everything worked!

Thanks again,

Carol

Kelly Moore

Phew! So glad that worked.

Let me know if you need any help tweaking the formula to differentiate between parent and child

cheers

Kelly

Carol Rykovanova

Hey Kelly,

Thanks you very very much! I worked on this formula for a long time and lost sight of the obvious :)

Of course, you're right, as soon as I moved the rest of the rows to a level below - everything worked!

Thanks again,

Carol