Counting Checkbox from parent and sub

Mohamed Eilyzar Abdullah

Hi

I am trying the below function to count the number of checkboxes in total:

=COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN())

but it is only counting the checkboxes of the main parent and not the sub tasks

Subtask "Ticket for Singapore" is not counted

Kelly Moore

Hello @Mohamed Eilyzar Abdullah

As you have discovered, the CHILDREN function will only count children that belong to that immediate parent. Singapore is not a Child of the Blue row but of the Implementation for Singapore row. To collect all children you will need to use the DESCENDANTS function

=COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row))

If your checkbox column is being checked using a formula, we might be able to differentiate the formulas so you can use a column formula.

=IF(COUNT(ANCESTORS([Primary column name]@row))=0, COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row)), insert your checkbox formula

This IF formula checks the hierarchy level of the row - which for your 'blue' row = zero. If the row is level zero, it does your count. Otherwise it does your checkbox formula.

cheers

Kelly

Kelly Moore

Hello @Mohamed Eilyzar Abdullah

As you have discovered, the CHILDREN function will only count children that belong to that immediate parent. Singapore is not a Child of the Blue row but of the Implementation for Singapore row. To collect all children you will need to use the DESCENDANTS function

=COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row))

If your checkbox column is being checked using a formula, we might be able to differentiate the formulas so you can use a column formula.

=IF(COUNT(ANCESTORS([Primary column name]@row))=0, COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row)), insert your checkbox formula

This IF formula checks the hierarchy level of the row - which for your 'blue' row = zero. If the row is level zero, it does your count. Otherwise it does your checkbox formula.

cheers

Kelly

Mohamed Eilyzar Abdullah

Hi

Thanks! It did worked for the light blue rows that counts the total checkboxes.

I changed it to :

=COUNTIF(DESCENDANTS(), 1) + " of " + COUNT(DESCENDANTS())

But somehow, when I use the above formula for the main parent task, row 1, ( 40 of 52 ), it counts in total the Parent task ( row 2, 4 ) + Checkboxes ( row 3, 5, 6 )

What will be a better formula for the main parent task, row 1 ( 40 of 52 ), that does not count the task ( row 2, 4 ), but only counts total checkboxes ( row 3, 5, 6 )? *Checkboxes has no formula

Kelly Moore

Hi @Mohamed Eilyzar Abdullah

When you're trying to differentiate between different levels of hierarchy, I find it easiest to add a helper column that calls out the hierarchical level. I typically name this text/number helper column as 'Level'.

The formula in the Level column is

= COUNT(ANCESTORS())

You'll note that your dark blue row is level zero. Your light blue rows are level one's. Assuming your entire sheet follows the pattern of your screenshot, all the checkboxes you're interested in are at level 2.

You have two formulas that you're interested in- Your dark blue rows and your light blue rows. We can combine this into one formula so you can add this to all your parent rows. IF you ever decide to check the child row boxes by formula, we can add that as well.

=IF(Level@row = 0, COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(Level@row), >1) + " of " + COUNTIFS(DESCENDANTS(Level@row), >1), IF(Level@row = 1, COUNTIF(DESCENDANTS([Row Complete]@row), 1) + " of " + COUNT(DESCENDANTS([Row Complete]@row))))

Does this work for you?

Kelly

Mohamed Eilyzar Abdullah

Hi

It didn't work. Or maybe I'm doing it wrongly.

But it is ok. I am not implementing it for now.

Kelly Moore

Hey

When you say it didn't work, did you get an error or an incorrect count? Did you add the helper column? Please advise and we can continue to work this until you're ready to implement