All boxes are checked when converting "=IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1)" to column formula

Athar

I have a project with parent and child rows with Check Box. I'm trying to use formula =IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1) so that when children are checked parent row is checked.

This formula works fine in individual parent cell but when I convert it to column formula all check boxed are checked.

Please advice @Paul Newcome. Thank you.

Paul Newcome

You would need to have two separate checkbox columns. One for them to actually check and another that will be automated.

The formula for the automated one would be...

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, [Manual Checkbox Column]@row, IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN([Manual Checkbox Column]@row), 1), 1)

Andrée Starå

Hi @Athar

I hope you're well and safe!

You can't have a formula and also be able to check the box manually in a column formula.

Try something like this instead in the Parent rows.

=IF(COUNTIF(CHILDREN(), 1) = COUNT(CHILDREN()), 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Athar

Thanks @Andrée Starå

Both the formulas are working fine when manually used/ entered in each parent row.

However, I'm surprised to see when I enter "=COUNT (CHILDREN())", it shows 0 for row(s) with No Child Rows which makes sense. But when used as Column Formula the row(s) with no child row(s) is/are also checked.

=IF(COUNTIF(CHILDREN(), 1) = COUNT(CHILDREN()), 1)

Is there any way we could modify the below formula such that it leaves the cell blank i.e. if not true write " " in cell?

=IF(COUNTIF(CHILDREN(), 0 >0, 0,1)

Thank you so much.

Andrée Starå

@Athar

Happy to help!

The issue is that you won't be able to check the box if there is a column formula and assume that you'd need to be able to do that, correct?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Athar

@Andrée Starå Partially Correct.

Let me briefly describe the problem again.

When Column Formula is used, all the cells (from parent and children's rows) in that column are checked. When I dug the issue by using the above formulas only in Rows with no Children Rows, the checkbox were checked due to which all parent rows were also checked and I can't uncheck it. That's from where the problem starts.

I want to modify the formula such that Rows with no Children Rows are not checked and I can do it manually. And all the parent rows are automatically checked using formula.

Thank you again for kind help and guidance.

Athar

Hi @Andrée Starå, any update?

Looking forward.

Paul Newcome

What is the purpose for checking the boxes?

Athar

@Paul Newcome Task name column and all the parent rows are locked due to important information contained therein.

I want the team members to give their input in child rows only, including task completion by checking the box.

Therefore, it will be very nice if I could have the formula that automate the work i.e. by checking the boxes of all parent rows whose child tasks have completed.

Paul Newcome

You would need to have two separate checkbox columns. One for them to actually check and another that will be automated.

The formula for the automated one would be...

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, [Manual Checkbox Column]@row, IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN([Manual Checkbox Column]@row), 1), 1)

Monica J

Hi Paul,

I have a similar requirement for checking parent row checkbox when all child rows are checked, i tried implementing the above formula you suggested, it worked well on TaskColumn(All checkboxes including parent row gets checked) but it does not check the ManualColumn parent row checkbox dynamically. Not sure if i am missing something here. Could you please help!

Paul Newcome

@Monica J I'm not sure I follow. Are you able to provide a screenshot for reference?

Monica J

Hi Paul,

I have attached a screenshot of the columns that i am referring to Completed column is the Manual column where the user will be checking off the tasks and Testing Checkbox column is my Helper column , when i add the formula you shred above in my Helper Column(Testing Checkbox) it works fine it checks all children's and checks off the Parent row, but my requirement is it should check off the Parent column in the Manual column(Completed). Hope i clarified the requirement.

Screenshot (63).png

Paul Newcome

@Monica J You would need to either enter a formula in the parent rows or you would need a helper column that marks each of the parent rows so that you can use a Change Cell automation to check the box.