RYGB Parent and Child Roll Up

Options

What am I doing wrong?


=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 1, "Green", "Blue")))), IF([Status 2]@row = "Complete", "Blue", IF([Status 2]@row = "Delayed", "Red", IF(AND([Status 2]@row = "Current", [End Date]@row=TODAY(2)), "Yellow", IF([Status 2]@row = "Upcoming"), "Green", "")))


I want CHILDREN:

Delayed = Red

Current + End Date is within 3 days = Yellow

Upcoming = Green

Complete = Blue


I want PARENT

If there are Red, Red

If there are no Red but are Yellow, Yellow

If there are no Red and No Yellow, but there are Green, Green

If everything is Blue, Blue

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @MWHMax

    Try this:

    =IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 1, "Green", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Blue"), "Blue", IF([Status 2]@row = "Complete", "Blue", IF([Status 2]@row = "Delayed", "Red", IF(AND([Status 2]@row = "Current", [End Date]@row = TODAY(2)), "Yellow", IF([Status 2]@row = "Upcoming", "Green"))))))))

    Will it work for you?

    Kelly

  • MWHMax
    MWHMax ✭✭
    Options

    Thank you for your quick response, this formula made everything "Blue".

  • ChrisUPC
    ChrisUPC ✭✭✭✭
    Options

    I used this formula on a sheet to determine if Status if CHILDREN were "Red", "Yellow" etc.

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

    I think yours would simply be:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!