I need assistance with a parent/child formula

I would like to return a specific value in a parent row when all the values of the children is equal to a specific value.

I would like the parent row to automatically show complete as soon as all the children rows are completed. The cell can be blank if all the values are not complete.

I used the following formula to get the result that I want:

=IF(COUNTIF(CHILDREN(), "Not started") = 3, "Not started", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", IF(COUNTIF(CHILDREN(), "Complete") = 3, "Complete", IF(COUNTIF(CHILDREN(), "Complete") > 0, "In progress"))))

My problem is that you have to physically go and change this formula everytime you add or delete a child row. Is it possible to have another formula that do not use the Countif function?

Find more posts tagged with

Formulas

functionality

Accepted answers

Debbie Sawyer

Have you tried this:

=IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "Not started", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Complete") > 0, "In progress"))))

I.e. changing the number of rows declared as 3 --> number of rows delared as COUNT(CHILDREN()) instead.

Hope this helps

Debbie

All comments

Debbie Sawyer

Have you tried this:

I.e. changing the number of rows declared as 3 --> number of rows delared as COUNT(CHILDREN()) instead.

Hope this helps

Debbie

Jana Brits

Thank you so much Debbie. This worked!

Debbie Sawyer

Excellent :)

Jana Brits

I noticed that all the children has to have a value in the sell for the formula to work. No cell can be blank.

The formula in the 100% cell: =(COUNTIF(CHILDREN(), "Complete") / COUNT(CHILDREN()))

The formula in the Complete cell: =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "")

Debbie Sawyer

You could nominate a column in the =COUNT(CHILDREN([Primary Column]@row)) which will always containt a value.

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.