Parent Row Status Roll Up

roblutz

Hello!

I'm looking for help on a formula I have yet to find exactly what I am looking for. I have found a couple of others I have been tweaking, but not working exactly how I would like. I'm looking for a formula to roll up the status of children status to the parent row. Here is the breakdown:

If all statuses are Not Started - Roll up is "Not Started"

If any status is In Progress - Roll up is "In Progress"

If all statuses are Complete - Roll up is "Complete"

If any child is "N/A" - it is counted as "Complete"

Any help would be greatly appreciated!

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

roblutz

Thank you that is PERFECT! :-)

Paul Newcome

Happy to help. 👍️

Liz O

@Paul Newcome How would you adjust the formula if I am tracking progress with the progress balls of empty, quarter, half, three quarter, and full? I would like to have it similar to roblutz where:

• if all are empty, the parent shows "Not Started"
• If there is a mixture of statuses, the parent shows "In Progress"
• If all are full, the parent shows "Completed"
• Any empty cells are not considered

I appreciate your assistance. :-)

Genevieve P.

Hi @Liz O

Thanks for providing your list of criteria!

Here's how I would write out your formula:

=IF(COUNT(CHILDREN()) = 0, "Not Started", IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(), "Completed"), "Completed", "In Progress"))

COUNT ignores blank cells, which means that the COUNT of Children, if they're all blank, will be 0.

The one thing I added in is for your "Completed" criteria, it would need to be all child cells not including blank child cells.

Notice how it says "In Progress" when 2/3 say "Completed" but one is blank. Is that what you want, or do you want that to say "Completed"?

Liz O

Thanks, @Genevieve P. for your suggestion.

When I copied it into my table it didn't work and said unparsable.

I am using the ball progression icons for the individual tasks. I would like the parent to either show the ball like it is now (I am manually changing the ball at this point) or if that isn't possible to show the words Not Started, In Progress or Completed, like is formulated above.

Genevieve P.

Hi @Liz O

Do you have a column titled "Primary Column"? If not, you'll need to change the text in the [square brackets] to match whatever column title you have above the text "Project Documents". I'll bold it below.

=IF(COUNT(CHILDREN()) = 0, "Not Started", IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(), "Completed"), "Completed", "In Progress"))

Thank you also for clarifying that you're using symbols. In this case we'll need to change the text we want to output, since instead of the word "Not Started" it looks like you want the symbol for an empty circle.

I also now understand that when you said "Empty" you meant the empty circle, not an empty or blank cell. In this case, we'll need to change the start of the formula as well. Here's the updated one with the words associated with your symbols:

=IF(COUNTIF(CHILDREN(), "Empty") = COUNT(CHILDREN()), "Empty", IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(), "Full"), "Full", "Quarter"))

Keep in mind this will return a "Quarter" symbol any time our criteria isn't met, meaning that if there are 3 tasks with a "Half" or "Three Quarter" then you will still see "Quarter" as the summary. If this isn't what you'd like, we'd need to know your exact requirements for every possible scenario in order to add it to the formula.

Let me know if this now works for you!

Genevieve