Count specific total number grand children rows and ignore higher rows
Hello All,
I am tracking different sessions being hosted between employees.
The sessions are the rows with the white background. I want to dynamically count the total number of sessions that are happening for row 2 "IMC".
So I need to count the number of children of row 3 "IMC Common Architecture" as well as the number of children of row 6 "IMC CAD" to come up with a total.
Right now just to make it work, I am using:
=COUNT(CHILDREN([Task Name]3), +COUNT(CHILDREN([Task Name]6)))
Which is somehow returning the number 3, rather than 11.
However, more light gray parent rows, like "IMC RMS" may be added a later date, so I would like to count the sessions under these dynamically and not have to edit my formula in the future.
I can't just count the children of row 2 IMC because then it would include the topic rows like "IMC Common Architecture".
Is this possible?
Comments
-
Try using a helper column with
=COUNT(ANCESTORS())
This will give you a 0 for parent rows, 1 for child rows, 2 for grandchildren rows, etc.
You can then use
=COUNTIFS([Helper Column]:[Helper Column], 2)
That will count how many rows have 2 ancestors aka grandchildren rows.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you for taking the time to reply Paul, this worked for me!
-
Excellent! Happy to help.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
tlawrence and Paul,
I almost always add + 1 in the end because then it will be the same number as in Card View.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Good thinking. I have never really had a use for Card View, so I never think about it.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Andree,
Thank you for the suggestion! Could you be a little more specific about what this should look like?
Do you mean simply: =COUNTIFS([Helper Column]:[Helper Column], 2) + 1
Wouldn't this throw the numbers off?
-
I was thinking about the hierarchy levels so yes it would not work for the above.
Formula for level: =COUNT(ANCESTORS()) + 1)
Happy Holidays & Happy New Year!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!