What does DESCENDANTS return? Can it be used for a range?
I am trying to make a task counter that counts task that are children of a particular cell. However, if they are children of children, it should only count the grandchildren.
=COUNT(DESCENDANTS($[Task List]$53)) works but counts child rows with children which I don't want to count.
I added a helper column that returns a 1 or a zero depending on whether I want to count the task or not. This basically says if the task does not have children, return 1, if it does have children, return 0 so it doesn't get counted.
=IF(COUNT(CHILDREN([Task List]@row)) = 0, 1, 0)
Then to get the total I would like:
=COUNTIF(DESCENDANTS($[Task List]$53), [H Project Phase]@row = 1)
But this returns 0
I don't think I understand what DESCENDATS returns without a count function in front of it.
I don't want to give a row reference because in testing the row reference did not track adding and removing rows, which will happen.
Answers
-
Hey @EMACTX
DESCENDANTS counts every single child row beneath a parent, regardless of level of indent.
If you only want to subtract the first level of child, I would use the CHILDREN function which will only see the first level.
Try something like:
=COUNT(DESCENDANTS($[Task List]$53)) - COUNT(CHILDREN($[Task List]$53))
However if there are times where you want to count the Children because there are no sub-tasks, then you can use an IF statement to figure this out:
=IF(COUNT(DESCENDANTS($[Task List]$53)) = COUNT(CHILDREN($[Task List]$53)), COUNT(DESCENDANTS($[Task List]$53), (COUNT(DESCENDANTS($[Task List]$53)) - COUNT(CHILDREN($[Task List]$53)))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 296 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!