Hi there, I am trying to do a formula that says something along the lines of the below: * If all tasks are marked as complete, update parent to Complete * If any task is marked as 'in progress' update parent to In Progress * If no tasks are started, update parent to Not Started * If majority of tasks are marked as Potential Risk OR Risk / Delay update parent to At Risk * If all tasks are NA / canceled, update parent to Canceled My status options are: Not Started In Progress Potential Risk Risk / Delay Complete NA /Canceled I tried looking at other community posts, and found this, but I'm not sure how to incorporate my additional statuses. =IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") > 0, "Not Started", "Complete")) Please can someone advise? Appreciate any help! Thanks! Rosie

Child task status rollup to Parent task

Hi there,

I am trying to do a formula that says something along the lines of the below:

If all tasks are marked as complete, update parent to Complete
If any task is marked as 'in progress' update parent to In Progress
If no tasks are started, update parent to Not Started
If majority of tasks are marked as Potential Risk OR Risk / Delay update parent to At Risk
If all tasks are NA / canceled, update parent to Canceled

My status options are:

	Not Started


	In Progress

	Potential Risk

	Risk / Delay

	Complete

	NA /Canceled

I tried looking at other community posts, and found this, but I'm not sure how to incorporate my additional statuses.

=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") > 0, "Not Started", "Complete"))

Please can someone advise? Appreciate any help!

Thanks!

Rosie

Find more posts tagged with

Accepted answers

All comments

Leela Lodhi

Hi RosieO,

To incorporate your additional statuses and meet your specific conditions, you can try below formula:

=IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))

I hope this helps!

Thanks!

Leela Lodhi

RosieO

Hi Leela, thanks so much for this!

I have one tiny thing thats not working, which is that if the child task is blank, the formula displays as 'Canceled' and I am not sure how to fix to say if the status is blank, display 'Not Started' OR display nothing. Just don't think that Canceled is accurate for this. See screengrab below.

Thank you so much for any additional guidance!!!

NicoLHC

@RosieO i suggest you to add one or two helper coloum to get a better overview where you are . =COUNT(ANCESTORS([Activity / Item]@row)) same for children.

Try this updated formula considering the blank. =IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled",
IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete",
IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress",
IF(COUNTIFS(CHILDREN(), "Not Started") + COUNTIFS(CHILDREN(), "") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))

RosieO

Thanks Nico for coming back so quickly. I already have about a million columns in my sheet so was trying to avoid adding anything else if I can.

Apologies for this, but I'm not sure what the difference is between the original formula Leela provided and the one you have provided above?

Thanks!

NicoLHC

@RosieO my formula accounts for both “Not Started” and blank child tasks by using the following condition:. i hope its alwasy bit more difficult to draft a formula if you not working in the actual sheet.

RosieO

Hm - I appreciate the help, but I am getting the same outcome in Smartsheet:

I published a test sheet here: https://app.smartsheet.com/b/publish?EQBCT=0eff829b424148d2b0e988979e4b6a40 if thats helpful?

Thanks in advance!

NicoLHC

@RosieO please check

94814934571841e38a1357bf7b5b0196-40e71945b38d5555-full-play.gif

https://www.loom.com/share/94814934571841e38a1357bf7b5b0196?sid=aa289c9f-38ac-4511-a928-d4ca2eb99364

https://app.smartsheet.com/b/publish?EQBCT=ff6c26995ad340abb445f1cc50649fdd

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.