My Formula Needs Help!

Hi, I have a status column with the following value: Not Started, In Progress, Canceled, On Hold, Complete.

I would like to modify my current formula to cause the behavior below:

All children are Complete – Parent = Complete
One or more children are Complete, In Process, and any other status – Parent = In Process
All children are On Hold – Parent = On Hold
All children are Not Started – Parent = Not Started
All children are Canceled – Parent = Canceled

My current formula looks like this:

=IF(OR(COUNT(CHILDREN(Subject@row)) = 0, Subject@row = ""), "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIF(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold"))))))

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this...

=IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNT(CHILDREN(Subject@row)), "Cancelled", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNT(CHILDREN(Subject@row)), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN(Subject@row)), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN(Subject@row)), "Not Started", "In Process"))))

All comments

Paul Newcome

Try this...

Kym L

Thanks Paul! For the most part this is working beautifully. The only glitch is when one or more of the children are Cancelled and the others are Complete, the Parent is showing as In Progress and would like Parent to show Complete in this case

Paul Newcome

Ok. Here is a little tweak:

=IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNT(CHILDREN(Subject@row)), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN(Subject@row)), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN(Subject@row)), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN(Subject@row)), "Not Started", "In Process"))))

Kym L

thanks!!!! perfect :-)

Paul Newcome

Happy to help. 👍️

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.