COUNTIF question with child rows
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
-
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
-
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
-
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
-
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
-
Thank you both! I got that to work :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!