Need Help Fixing Formula in a Timeline with Subtasks

I used this formula in the past and it worked, but I no longer want the cell to have an updated Status of "Complete" if all sub rows are marked "Complete" or "Not Applicable". I would like to change it to return a Status of "Complete" if ALL sub rows are "Complete" and a Status of "Not Applicable" if ALL sub rows are marked "Not Applicable". I would like to keep all conditions/returned Status values the same after the first COUNTIF formula:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "Not Applicable")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress")))))

This is the best new formula I have developed, but it returns an error message consistently:

=IF(COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(COUNTIF(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress"))))))

Any help is much appreciated! Thank you

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What would be the output if it is a mixture of both Complete and Not Applicable?

ocak

Any rows that have a value of "Not Applicable" in the Status column are automated to be deleted off of the timeline once saved. So, when the "Not Applicable" rows are gone, only the "Complete" lines will remain, making the parent Status "Complete" as well.

Paul Newcome

Ok. Give this a try then. It removes the "Not Applicable" argument from everything except for when all are "Not Applicable" since those will be removed from the sheet:

=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNT(DISTINCT(CHILDREN())) = 1, INDEX(CHILDREN(), 1), "In Progress")

The first will output "Not Applicable" if all are "Not Applicable".

Next we check to see how many different children there are. If all of them match, we output that. This allows us to accommodate all statuses where all children are the same without having to spell out each status individually.

Finally, if there is a mix of statuses, it will output "In Progress". This covers a mix of all three, some Complete and some Not Started, some Complete and some In Progress, and some In Progress and some Not Started (all remaining variations).

ocak

This worked! Thanks so much for the help Paul

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