# COUNTIF question with child rows

Options

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!

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

Thank you both! I got that to work :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!