I want to count number of days in a separate column,,,
I want to count the number of days in a separate column. I have a "Primary Column" with a parent row that counts the number of Weeks. The 'CHILDREN' rows are for each Week Ending with working days as 'GRANDCHILD' rows.
I have a separate column named 'Days Count' where I want to count the total 'GRANDCHILD' rows in the 'Primary Column'.
I have tried various formulas but they have all been unsuccessful. Can someone help?
Answers
-
hi @TeeM;
What about creating helper column called e.g. "Amount of Children" and use the formula in it =count(children(name_of_primary_column));
it will show 0 for all the grandchildren and in the extra column you use: =countif("range", 0) so it will give you number of grandchildren.
Hope it helps.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
Hi @Tomasz Kowalski,
Unfortunately, that doesn't seem to work
-
@TeeM - Another idea you could try would be to add a column called "Level" and count the number of Ancestors. That would identify which rows are your "child" rows, "grandchild" rows, etc. - and you could reference this in your formula to count the number of days/grandchild rows.
Your "Level" column would look like this:
=COUNT(ANCESTORS([Primary Column]@row))
and your "Days Count" column could look like this:
=IF(Level@row=1, COUNT(CHILDREN(level@row)))
The "Days Count" formula will only count the number of children if the Level is 1 — your "week ending" rows. If you wanted the total to roll all the way up, you could add another IF statement:
=IF(Level@row=1, COUNT(CHILDREN(Level@row)), IF(Level@row=0, SUM(CHILDREN([Days Count]@row))))
The second "If" statement will sum your "week ending" rows in the parent row.
I'm not sure if this is exactly what you're after, though, so let us know! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!