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
-
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
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!