Check off a parent row when one or the other checkboxes in a child row is checked

WFeagin

I have a sheet with parent rows that roll up a set of child rows. The child rows have 2 columns that are not applicable or complete...once ALL the child rows have either Not Applicable OR Complete, I'd like to check of Complete in the parent row. I can get my formula to check off the parent if all of the child rows are completed in a single column, but how do I mix and match between columns? I'm including a screen shot of the sheet.

Thanks for any help anyone can provide

Devin Lee

Hey @WFeagin

This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.

=IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)

Devin Lee

Hey @WFeagin

This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.

=IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)

WFeagin

Thanks so much @Devin Lee! For it to work for me, I had to alter it slightly to:

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)

When I tried it with IF(COUNT(CHILDREN(Primary@row)) I got an unparseable error. The change also allowed me to paste the formula into each parent row without having to edit it based on row location. It works fantastically, butt ime for me to practice more!

Again, thanks so much