"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"))))

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

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

Paul Newcome

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")))))

Genevieve P.

https://community.smartsheet.com/discussion/comment/336454#Comment_336454

Great solution, @Paul Newcome 😉

Glad it worked for you, @Rachel R. !

All comments

Genevieve P.

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:

Cheers,

Genevieve

Paul Newcome

Try this:

Rachel R.

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.

Genevieve P.

https://community.smartsheet.com/discussion/comment/336454#Comment_336454

Great solution, @Paul Newcome 😉

Glad it worked for you, @Rachel R. !

Paul Newcome

@Rachel R. Happy to help. 👍️

@Genevieve P. We REALLY need that indicator that someone is actively typing a response. Hahaha.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.