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, 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
-
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
-
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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!