Formula to Roll Up Task Status

JL2022

Hi All!

I am looking for some help with a formula that I haven't been able to find in the community already.

I'd like to roll up my Status column to the parent rows with the following conditions:

• Parent shows as Not Started if: All children are either Cancelled or Not Started.
• Parent shows as In Progress if: Even one child is In Progress.
• Parent shows as Complete if: All children are either Cancelled or Complete.
• Parent shows as Cancelled if: All children are Cancelled.
• Parent shows as On Hold if: All children are either Cancelled or On Hold.

One other "feature" that would be helpful is if I was able to make it a column formula and have it only apply to parent rows (I know there needs to be an IF PARENT in there somewhere).

Thank you!!

Kelly Moore

Hey JL2022

For the adjustment on the blanks, I removed the reference to your primary column in the Count function. I think that will take care of the blanks

=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold")))))

As far as the column formula - I suspected you might be completing the Child row status manually. As the status column is shared by both Parent and Child, you cannot have a column formula that allows manual entry. A column formula will not be possible in this column. With the addition of a helper column for your child row manual status we could build a column formula. Please advise if you're interested in this approach.

Kelly

JL2022

@Genevieve P. tagging you here since you were SO helpful the last time I had an issue. :)

Kelly Moore

Hey @JL2022

The formula below should get you started on your rollup.

=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN(), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold")))))

Before I could convert the formula above to a column formula I need some information about how the child-row status is currently being determined. What is the formula you are currently using for those rows?

Kelly

JL2022

Hi @Kelly Moore - thanks so much! It looks like it works for all of the parents where no children are blank, but if there's a blank child, the parent stays blank. Is there an adjustment I can make for that?

Regarding your question - there is no formula for child status. Those are entered manually.

Kelly Moore

Hey JL2022

For the adjustment on the blanks, I removed the reference to your primary column in the Count function. I think that will take care of the blanks

=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold")))))

As far as the column formula - I suspected you might be completing the Child row status manually. As the status column is shared by both Parent and Child, you cannot have a column formula that allows manual entry. A column formula will not be possible in this column. With the addition of a helper column for your child row manual status we could build a column formula. Please advise if you're interested in this approach.

Kelly

JL2022

@Kelly Moore Sorry for the delayed response. I have a helper column (checkbox) to indicate parent rows (originally created to exclude those from sheet summary calculations). Does that help?

Also, I accidently marked the the question as answered before I tested the formula, and it is returning "Cancelled" in almost every parent. Did I do something incorrectly?

Thank you so much for your help.

Kelly Moore

Hey

To answer the column formula question first - no, your parent row indicator doesn't help. A column formula is all or nothing. A formula would be applied to the child rows essentially saying keep this blank. The blank (looking) cells would not be available to put values in. The column would be locked to manual entries. As mentioned earlier, if you used a helper column, you could add the manual entry there. Then, we would use your Parent checkbox column in an IF statement to differentiate where the formula was supposed to gather information from. I can help if this is what you want.

I just tested the above formula in my sheet - it appears to work. Would you mind copying and pasting again into your sheet? If the problem persists, is it possible to get a screenshot of sheet and one of formula with the colored text?

Kelly