Ancestor Formula for RYG


I am trying to create a Status for the Overall Project using the Ancestor formula based the Parent-Child status. It is NOT based on %. I cannot seem to make it work. It works well for the Parent-Child but then when i try to go a level up it is Unparseable :(

This is what I have at the Parent level

=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Green") > 0), "Yellow")))

Any suggestions/help will be appreciated!



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Elizabeth Riebeling

    Can you post a screen capture of your sheet and the levels, but block out sensitive data?

    The formula you have there should work on higher levels, however the "Children" of a higher level would be a "Parent" row of other rows, does that make sense?

    So if you had 1 Grandparent row, 3 Parent rows, and 10 Child rows, the formula in the Grandparent Row would only look at the 3 Parent row cells. However those 3 Parent rows will have their formula based on the Child rows.

    Do you want something different for the higher levels?

  • Elizabeth Riebeling
    edited 03/16/22

    Hi and thank you for responding!

    It makes sense but i don't know how to make it hone in on ONLY the parent rows

    This is not thr RYG but same concept

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Elizabeth Riebeling

    If you put the same formula in a "Grandparent" row then it will hone in on only the Parent rows (since those are the Children of the Grandparent). Is that what you're looking to do?

    Using your screen capture, you should be able to place this formula in the "Project Charter" row and it will look at the two darker blue rows.

    Then if you put the exact same formula (no edits needed) in the "Initiation Phase" row, it will look at the "Project Charter" and "Identify Stakeholders" levels.

    If this isn't what you want it to do, could you explain how you want the formula to calculate, using the screen capture for reference?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!