Aggregate Status Circles w/ Formula
Hi, I have these multiple statuses across 4 projects (each project is one of the rows) within 1 program and I want to be able to incorporate a formula where I can summarize all of these to a single row with the same columns in a separate sheet to present at the program level.
Formula to say: If there's at least 1 yellow ball in the Overall column, produce yellow ball. If every status circle in the respective column is green, produce a green ball. If there's at least 1 red ball in a column, the roll up view should produce a red ball.
I saw this formula on another column, but don't think I need the 'CHILDREN' part of the formula so not sure how to accommodate to my ask. 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"))
Best Answer
-
Try something like this...
=IF(COUNTIFS(Overall@row:Value@row, "Red")> 0, "Red", IF(COUNTIFS(Overall@row:Value@row, "Yellow")> 0, "Yellow", "Green"))
Answers
-
Try something like this...
=IF(COUNTIFS(Overall@row:Value@row, "Red")> 0, "Red", IF(COUNTIFS(Overall@row:Value@row, "Yellow")> 0, "Yellow", "Green"))
-
Thanks Paul. I tried this formula to capture the projects underneath the program level =IF(COUNTIFS(Overall12:Overall14, "Red") > 0, "Red", IF(COUNTIFS(Overall12:Overall14, "Yellow") > 0, "Yellow", "Green")).
So then the formula changes across based on the Column, so for Budget, it's =IF(COUNTIFS(Budget12:Budget14, "Red") > 0, "Red", IF(COUNTIFS(Budget12:Budget14, "Yellow") > 0, "Yellow", "Green")).
Do you have any suggestions on how to improve the formula? Very open to feedback. I would be linking the underlying status circles (DPC O, DPC A, IM) from other sheets.
-
If you are looking at the child rows in the same column, then you would want to replace the range with CHIDREN().
Help Article Resources
Categories
Check out the Formula Handbook template!