"At Risk" Status Formula

Rachel R.
Rachel R. ✭✭
edited 08/15/22 in Formulas and Functions

I have the following formula for setting the parent status. I want the parent status to change to "At Risk" should even just 1 child have an "At Risk" status. I am having a hard time figuring out how to add this criteria to this formula:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN(Tasks@row)), "Complete", IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN(Tasks@row)), "Not Started", IF(COUNTIF(CHILDREN(), <>"") > 0, "In Process", IF(COUNTIF(CHILDREN(), "Complete") = 0, "Not Started"))))

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 07/18/22 Answer ✓

    Hi @Rachel R.

    You'll want to add this as your very first criteria so that it's the first thing your formula searches for:

    =IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk"

    This counts how many child cells say "At Risk" and if it's greater than 0 (so even one cell), then return "At Risk" in the Parent row.

    Then you can move on to the rest of your formula:

    =IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN(Tasks@row)), "Complete", IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN(Tasks@row)), "Not Started", IF(COUNTIF(CHILDREN(), <>"") > 0, "In Process", IF(COUNTIF(CHILDREN(), "Complete") = 0, "Not Started")))))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF(COUNTIFS(CHILDREN(), @cell = "At Risk")> 0, "At Risk", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN(Tasks@row)), "Complete", IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN(Tasks@row)), "Not Started", IF(COUNTIF(CHILDREN(), <>"") > 0, "In Process", IF(COUNTIF(CHILDREN(), "Complete") = 0, "Not Started")))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!