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!



Best Answers

  • Casey Lisak
    Casey Lisak ✭✭✭
    Answer ✓
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Casey Lisak
    Casey Lisak ✭✭✭
    Answer ✓
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    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!