COUNTIF question with child rows

Anna Deschenes

I'm trying to get the dark blue row (46) to display the number of light blue row sections that are complete, but I'm not sure how to write the formula with that "of" in the cell. Thank you in advance!

Casey Lisak

There are a couple ways to this

One that comes to mind is: you can setup a "helper" column that will check to see if all the children are checked. You can use a formula like this:

In the helper column, I would put a formula like this:

=IF(COUNTIF(CHILDREN([Task Complete]@row), =1) = COUNT(CHILDREN([Task Complete]@row)), "Done", "Not Done")

Then you need a count formula to count the "Done" projects

=COUNTIF([Helper Col]:[Helper Col], "Done")

In my screenshot the Grandparent would be your dark blue row

Hope this helps,

Casey

Kelly Moore

Hi @Anna Deschenes

Building on @Casey Lisak answer, with one more helper column you can find the 'dark blue' parent rows.

The ANCESTOR function traces hierarchy. A Grandparent will have a level of zero. When you do your COUNTIFS, you'll be able to pull all of the completed parents with whatever descriptor is used (eg, 'completed', a checkbox, etc) AND add the relevant hierarchical level to only pull the grandparents.

=COUNT(ANCESTORS())

cheers,

Kelly

Casey Lisak

There are a couple ways to this

One that comes to mind is: you can setup a "helper" column that will check to see if all the children are checked. You can use a formula like this:

In the helper column, I would put a formula like this:

=IF(COUNTIF(CHILDREN([Task Complete]@row), =1) = COUNT(CHILDREN([Task Complete]@row)), "Done", "Not Done")

Then you need a count formula to count the "Done" projects

=COUNTIF([Helper Col]:[Helper Col], "Done")

In my screenshot the Grandparent would be your dark blue row

Hope this helps,

Casey

Kelly Moore

Hi @Anna Deschenes

Building on @Casey Lisak answer, with one more helper column you can find the 'dark blue' parent rows.

The ANCESTOR function traces hierarchy. A Grandparent will have a level of zero. When you do your COUNTIFS, you'll be able to pull all of the completed parents with whatever descriptor is used (eg, 'completed', a checkbox, etc) AND add the relevant hierarchical level to only pull the grandparents.

=COUNT(ANCESTORS())

cheers,

Kelly

Anna Deschenes

Thank you both! I got that to work :)