# Aggregate Status Circles w/ Formula

Options

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!