Parent Row Status Formula Help

In the Project Roll-up Template, I'd like to add a "Not Applicable" as a status selection for rows that won't be applicable. If a single children row is "Not Applicable", I don't want the parent row status to change, if that makes sense. Currently if a single child row is "Not Applicable", the parent row status goes to "In Progress".

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))


Best Answer

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓

    Hi @Reggie Andaya

    The logic is simple, just add additional counting for "Not Applicable"


    =IF(COUNTIF(CHILDREN(), "Complete")+COUNTIF(CHILDREN(), "Not Applicable") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started")+COUNTIF(CHILDREN(), "Not Applicable") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))


    Please let me know if that works for you?

    Thanks!

    Tomasz Giba

Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓

    Hi @Reggie Andaya

    The logic is simple, just add additional counting for "Not Applicable"


    =IF(COUNTIF(CHILDREN(), "Complete")+COUNTIF(CHILDREN(), "Not Applicable") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started")+COUNTIF(CHILDREN(), "Not Applicable") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))


    Please let me know if that works for you?

    Thanks!

    Tomasz Giba

  • @Tomasz Giba Yes thanks so much for your help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!