Is there a formula to specify "Completed" if children "Completed" or "Nothing to Submit"?

The formula I'm using currently returns the cell as blank if children are "completed" or "nothing to submit", but I would like it to return "Completed" in that case.

=IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Nothing to Submit") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "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(), "Blocked") > 0, "In Progress")))))

Find more posts tagged with

Accepted answers

Paul Newcome

Oh! I see now.

Go back to CHILDREN but update "Complete" to "Completed".

All comments

Paul Newcome

Try using DESCENDANTS instead of CHILDREN.

Sara Ross

Unfortunately, even with this change the status cells for the parents ("Provide Submissions" and "Proposed Rec Dept Submissions") remain blank.

This is the updated formula I used...

=IF(COUNTIF(DESCENDANTS(), "Complete") + COUNTIF(DESCENDANTS(), "Nothing to Submit") = COUNT(DESCENDANTS()), "Complete", IF(COUNTIF(DESCENDANTS(), "Not Started") = COUNT(DESCENDANTS()), "Not Started", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Not Started") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Blocked") > 0, "In Progress")))))

Any other suggestions?

Thanks,

Sara

Paul Newcome

Oh! I see now.

Go back to CHILDREN but update "Complete" to "Completed".

Sara Ross

But of course! Ugh...the little details.

Thanks for being a second set of eyes!

Sara

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.