Aggregate Status Circles w/ Formula

anna2121

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"))

Paul Newcome

Try something like this...

=IF(COUNTIFS(Overall@row:Value@row, "Red")> 0, "Red", IF(COUNTIFS(Overall@row:Value@row, "Yellow")> 0, "Yellow", "Green"))

Paul Newcome

Try something like this...

=IF(COUNTIFS(Overall@row:Value@row, "Red")> 0, "Red", IF(COUNTIFS(Overall@row:Value@row, "Yellow")> 0, "Yellow", "Green"))

anna2121

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.

Paul Newcome

If you are looking at the child rows in the same column, then you would want to replace the range with CHIDREN().