Setting parent value based on multiple criteria and multiple columns of children

Rowie

Hello community,

Ive scoured the forums, and found some similar questions, but nothing quite like what I need. I'm new at this app and forum, so please advise if I make a mistake in how to ask properly.

I have parent rows, with an unknown number of children underneath. I'm trying to automate the status of the parent based on statuses of the children (which are set manually via a dropdown menu on each child row). I have it working (possibly inefficiently) except for one item (#8).

What I'm trying to accomplish is this.

1. If the ToDo checkbox on parent row is unchecked, the status is NA
2. If any of the children statuses are "Blocked (High Pri)" then set status of parent to match
3. If any of the children statuses are "Start ASAP" then set status of parent to match
4. If any of the children statuses are "In Progress" then set status of parent to match
5. If any of the children statuses are "Blocked (Low Pri)" then set status of parent to match
6. If any of the children statuses are "To Do Later" then set status of parent to match
7. If all children statuses are blank, set status to "Not Started"
8. Here is the problematic one : Currently, if all children statuses are any combination of Complete, Noted/Closed, or NA, I set parent status to "Complete". What I want to do: completely remove the NA status from the children's status dropdown list, have the user uncheck the "ToDo" checkbox instead when something is Not Applicable, and have the formula check for whether the checkbox in the children's "ToDo" column is unchecked, as part of the OR sequence, replacing the NA status check. So the new logic would be to set parent status to Complete if all children have any combination of Status = Complete, Status = Noted/Closed, or ToDo checkbox is off)
9. If none of these match (which in this case should only occur when there are some marked Complete or Noted/Closed, and remaining children are blank) then set status to "In Progress"

Here is my current formula. It works, except I cant figure out how to make the desired change in point 8

=IF(ToDo@row = False, "NA", IF(SUM((COUNTIF(CHILDREN(), "")),COUNT(CHILDREN())) = COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "NA", @cell = "Noted/Closed")), "Complete", IF(COUNTIF(CHILDREN(), "Blocked (High Pri)") > 0, "Blocked (High Pri)", IF(COUNTIF(CHILDREN(), "Start ASAP") > 0, "Start ASAP", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked (Low Pri)") > 0, "Blocked (Low Pri)", IF(COUNTIF(CHILDREN(), "To Do Later") > 0, "To Do Later", IF(SUM((COUNTIF(CHILDREN(), "")),COUNT(CHILDREN())) = COUNTIF(CHILDREN(), ""), "Not Started", "In Progress")

In the attached screenshot, the parent status is "In Progress" but it would be "Complete" after the formula change, because the one child row that isnt marked Complete or Noted/Closed has it's ToDo checkbox unchecked.

I also welcome general help if the formula can be improved. Thank you so much for any help!!

Mark Cronk

Try:

=IF(ToDo@row = False, "NA", IF(SUM(COUNTIF(CHILDREN(), ""),COUNT(CHILDREN())))) = (COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Noted/Closed")+COUNTIF(Children([ToDo]), @cell=1)), "Complete", IF(COUNTIF(CHILDREN(), "Blocked (High Pri)") > 0, "Blocked (High Pri)", IF(COUNTIF(CHILDREN(), "Start ASAP") > 0, "Start ASAP", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked (Low Pri)") > 0, "Blocked (Low Pri)", IF(COUNTIF(CHILDREN(), "To Do Later") > 0, "To Do Later", IF(SUM((COUNTIF(CHILDREN(), "")),COUNT(CHILDREN())) = COUNTIF(CHILDREN(), ""), "Not Started", "In Progress"))))))))

Mark

Rowie

Thanks for the suggestion! I had actually worked out a similar solution using SUM instead of +:

=IF(ToDo@row = false, "NA", IF(COUNTIF(CHILDREN(), "Blocked (High Pri)") > 0, "Blocked (High Pri)", IF(COUNTIF(CHILDREN(), "Start ASAP") > 0, "Start ASAP", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked (Low Pri)") > 0, "Blocked (Low Pri)", IF(COUNTIF(CHILDREN(), "To Do Later") > 0, "To Do Later", IF(SUM((COUNTIF(CHILDREN(), "")), COUNT(CHILDREN())) = SUM(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Noted/Closed")), COUNTIFS(CHILDREN(ToDo@row), =0)), "Complete", IF(SUM((COUNTIF(CHILDREN(), "")), COUNT(CHILDREN())) = COUNTIF(CHILDREN(), ""), "Not Started", "In Progress"))))))))

Neither of these is fully airtight (but its good enough), since someone may put "Complete" into the status, and then uncheck ToDo, throwing off the counts. What would be ideal, is that for each row, if it has any of Status = Complete, or Status = Noted/Closed, or ToDo equals off, then increase count by 1. Then if the total count matches the total number of children, mark header status as Complete.

Unless theres a good way to do the above, I'll proceed with what we have. Is there a functional difference between your version and mine? I like yours better just because its easier to spot whats going on with the "+" among the sea of parens, but wondering about functional differences or efficiency.

And is the formula as a whole as efficient as possible? I'm keen on increasing efficiency since even with what I consider relatively few headers containing this formula (~20) the sheet seems noticeably slower to respond.

Thanks for the help