Count for Checklist with grandchildren

Michael Ouziel

Hi All,

I'm working on a large "checklist" that has children lines and grandchildren. On this checklist I have a completed column with a check box. So I was using the =COUNTIF(CHILDREN(RANGE),0) to get a list of all children lines that were incomplete. The problem is that as the list has evolved I now need it to do this for just the grandchildren. Does anyone know of a way to modify the formula to only show incomplete grandchildren?

Mike Wilday

Have you tried =Countif(Children(Children(Range),0))

Not sure if it will work... but give it a try! 

J. Craig Williams

I could not get this to work in a single formula*

To solve it, I added a column [Formula] to determine the ANCESTOR count of my ChkBox column

=COUNT(ANCESTORS(ChkBox23))

for row 23

This returns 0 for grand-parents, 1 for parents, and 2 for grand-children

The formula uses COUNTIFS to look at the ChkBox column for unchecked boxes and the Formula column for 2 (grand-children).

=COUNTIFS(ChkBox:ChkBox, 0, Formula:Formula, 2)

The formula is somewhere else so can look at the whole columns.

Hope this helps.

Craig

* My thinking was to use COUNT(ANCESTORS())=2 in the second COUNTIFS criteria, but everything I tried did not work.

 

 

 

 

 

 

Michael Ouziel

Hi Craig,

This does work, but on the sheet I am working on I do not have the option of adding a column (I am not the owner of the sheet). 

I feel like there are a bunch of formulas that can solve this issue if the Ancestors function could work on a range. Like if I could use COUNTIF(ANCESTORS(RANGE),<=1) and just subtract that form the total of checked unchecked boxes. 

J. Craig Williams

I think there is a bug in the ANCESTORS() function, but haven't played with it enough to prove it to myself.

Craig