"At Risk" Status Formula
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"))))
Best Answers
-
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 -
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!
-
Great solution, @Paul Newcome 😉
Glad it worked for you, @Rachel R. !
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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 -
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!
-
Thank you Genevieve and Paul! This worked and I am now getting the result I was looking for. I am glad this was an easy solution.
-
Great solution, @Paul Newcome 😉
Glad it worked for you, @Rachel R. !
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Rachel R. Happy to help. 👍️
@Genevieve P. We REALLY need that indicator that someone is actively typing a response. Hahaha.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!