Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count for Checklist with grandchildren

Options

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?

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 05/12/17
    Options

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

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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

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

    Craig

This discussion has been closed.