Formula for rolling up children status to parent rows

Hello,

I am trying to simply roll-up my children status' to their parent row. When using this formula:

=IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

I receive "Unparsable".

My status column is set up with Not Started, In Progress and Complete. I've done this before but cannot for the life of my remember how to easily do this.

Any help is appreciated!

Find more posts tagged with

Sheets

Formulas

Support

Project Management

Comments and Conversations

Accepted answers

Cassie L

@anglolauren 1. COUNTIFS does not support functions like OR() within its criteria. —>This one:

COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))

2. The condition @cell = "" OR @cell <> "" is always true (will always be blank or not blank), so it's essentially trying to count all child rows. But there's a simpler way to do that:

COUNT(CHILDREN())

3. Give this formula a try:

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

All comments

Cassie L

@anglolauren 1. COUNTIFS does not support functions like OR() within its criteria. —>This one:

COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))

2. The condition @cell = "" OR @cell <> "" is always true (will always be blank or not blank), so it's essentially trying to count all child rows. But there's a simpler way to do that:

COUNT(CHILDREN())

3. Give this formula a try:

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

anglolauren

Thank you @Cassie L this worked!

Cassie L

Glad to hear it!

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.