Counting Checkbox from parent and sub

Hi


I am trying the below function to count the number of checkboxes in total:

=COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN())

but it is only counting the checkboxes of the main parent and not the sub tasks

Subtask "Ticket for Singapore" is not counted



Best Answer

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

    Hello @Mohamed Eilyzar Abdullah

    As you have discovered, the CHILDREN function will only count children that belong to that immediate parent. Singapore is not a Child of the Blue row but of the Implementation for Singapore row. To collect all children you will need to use the DESCENDANTS function

    =COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row))

    If your checkbox column is being checked using a formula, we might be able to differentiate the formulas so you can use a column formula.

    =IF(COUNT(ANCESTORS([Primary column name]@row))=0, COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row)), insert your checkbox formula

    This IF formula checks the hierarchy level of the row - which for your 'blue' row = zero. If the row is level zero, it does your count. Otherwise it does your checkbox formula.

    cheers

    Kelly

Answers

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

    Hello @Mohamed Eilyzar Abdullah

    As you have discovered, the CHILDREN function will only count children that belong to that immediate parent. Singapore is not a Child of the Blue row but of the Implementation for Singapore row. To collect all children you will need to use the DESCENDANTS function

    =COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row))

    If your checkbox column is being checked using a formula, we might be able to differentiate the formulas so you can use a column formula.

    =IF(COUNT(ANCESTORS([Primary column name]@row))=0, COUNT(DESCENDANTS()) + "OF" + COUNT(DECENDANTS([Primary column name]@row)), insert your checkbox formula

    This IF formula checks the hierarchy level of the row - which for your 'blue' row = zero. If the row is level zero, it does your count. Otherwise it does your checkbox formula.

    cheers

    Kelly

  • Hi

    Thanks! It did worked for the light blue rows that counts the total checkboxes.

    I changed it to :

    =COUNTIF(DESCENDANTS(), 1) + " of " + COUNT(DESCENDANTS())

    But somehow, when I use the above formula for the main parent task, row 1, ( 40 of 52 ), it counts in total the Parent task ( row 2, 4 ) + Checkboxes ( row 3, 5, 6 )

    What will be a better formula for the main parent task, row 1 ( 40 of 52 ), that does not count the task ( row 2, 4 ), but only counts total checkboxes ( row 3, 5, 6 )? *Checkboxes has no formula

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hi @Mohamed Eilyzar Abdullah

    When you're trying to differentiate between different levels of hierarchy, I find it easiest to add a helper column that calls out the hierarchical level. I typically name this text/number helper column as 'Level'.

    The formula in the Level column is

    = COUNT(ANCESTORS())

    You'll note that your dark blue row is level zero. Your light blue rows are level one's. Assuming your entire sheet follows the pattern of your screenshot, all the checkboxes you're interested in are at level 2.

    You have two formulas that you're interested in- Your dark blue rows and your light blue rows. We can combine this into one formula so you can add this to all your parent rows. IF you ever decide to check the child row boxes by formula, we can add that as well.

    =IF(Level@row = 0, COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(Level@row), >1) + " of " + COUNTIFS(DESCENDANTS(Level@row), >1), IF(Level@row = 1, COUNTIF(DESCENDANTS([Row Complete]@row), 1) + " of " + COUNT(DESCENDANTS([Row Complete]@row))))

    Does this work for you?

    Kelly

  • Hi

    It didn't work. Or maybe I'm doing it wrongly.

    But it is ok. I am not implementing it for now.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey

    When you say it didn't work, did you get an error or an incorrect count? Did you add the helper column? Please advise and we can continue to work this until you're ready to implement

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!