How to sum up column RGY statuses?
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
Best Answer

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
Answers

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

Hi Kelly!
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, 2433), and, accordingly, in rows 3, 7, 2433, 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

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 2432. Because row7 is red, Overall Parent3 is red
Sub Parent at row 7.
It gets it's status from children 823. 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 nonparent row formula>.
Let me know how I can help.
Kelly

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

Phew! So glad that worked.
Let me know if you need any help tweaking the formula to differentiate between parent and child
cheers
Kelly

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!