Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Counting number of tasks, by top level then by RAG status
Hi everyone
My project has 4 Level 1 Tasks and about 300 sub tasks at up to 5 levels. I've been asked to count the 300 tasks by top level task, split by RAG
i.e.
Top Level Task 1 (100)
- Red (3)
- Amber (27)
- Green (70)
Top Level Task 2 (150)
- Red (2)
- Amber (8)
- Green (140)
etc
The card view is good for counting RAG, but only by one or all levels.
I thought the Ancestors function might be able to do this for each top level item, but I can't get that to work; neither does reporting seem to get any sort of counting going.
Would appreciate someone pointing me in the right direction.
Regards
Ward
Comments
-
Hi
What I suggest you do is using Hierarchy to Hide/ expand it, create a dashboard at the top of the project sheet that uses Formulaic calculations to populate the KPIs you indicate are important in the sheet below.
You could even then create a Master statistics sheet showing ALL your Projects and their KPIs by using Cell Linking.
Good design of the workflow is needed but we do offer a free initial consultation if you want to see this working?
Hope that helps?
RichardR
-
Not sure if this would help. I've used the RYG counting at a top level with the following:
="R(" + COUNTIF(CHILDREN(), "Red") + "), Y(" + COUNTIF(CHILDREN(), "Yellow") + "), G(" + COUNTIF(CHILDREN(), "Green") + ")"
and it will return something like:
R(2), Y(7), G(4) but as you've said, it will do that for the current children.RE: Ancestor, I've used it with conditional formatting to change row colors based on where they have been placed in a hierarchy.
=IFERROR(COUNT(CHILDREN([Primary Column]40)) + " - " + COUNT(ANCESTORS([Primary Column]40)) + " - " + PARENT([Primary Column]40) + " Phase:" + INDEX(ANCESTORS([Primary Column]40), 1), "- Header Row -")That returns "- Header Row -" for any root level rows and the following basd on how deeply the row is nested.
2 - 1 - be used to organize - Building Phase Phase:be used to organize - Building Phase
0 - 2 - a complex sheet Phase:be used to organize - Building PhaseThinking though your issue you may want to see if you can address it in two steps, maybe a column added that does an index count and then a formula that checks color at that index.
Not a solution to your problem but maybe something in there helps.
Cheers.
Oh, here's my formula 'scratch pad'.
https://app.smartsheet.com/b/publish?EQBCT=a0123120a0b747aebd8162322e85f01d
-
I'll give this a shot. You let me know if this works out.
This formula will go in each parent Top Level Task row, in the "Status" column. It will count each color and show in the parent row.
="Red-" + COUNTIF(CHILDREN(), ="RED") + ", Yellow-" + COUNTIF(CHILDREN(), ="YELLOW") + ", Green-" + COUNTIF(CHILDREN(), ="GREEN")
This formula will go in a Top Top Summary parent row to count all statuses for all tasks. I used the Column name of "Status". Please update for your column name.
="Red-" + COUNTIF(Status:Status, ="RED") + ", Yellow-" + COUNTIF(Status:Status, ="YELLOW") + ", Green-" + COUNTIF(Status:Status, ="GREEN")
Go for it
Shawn
-
Richard, Jason and SmSulli ... thanks for your great suggestions. I'll take them for a gallop over the weekend.
Your suggestions are looking a lot more elegant than my effort last evening ... using the joinancestors function to create a string of task titles in each row:
=IF(JOIN(ANCESTORS([Task Name]1)) = "", [Task Name]1, JOIN(ANCESTORS([Task Name]139)))
Then a nested if statement ...
if(left([stream breadcrumbs]1,13)="Stream Name 1","Stream Name 1",if(left([stream breadcrumbs]1,13)= etc, etc
Then creating a filter for each stream, viewing in card view. Gotta love workarounds, but not as much as doing it the right way so tks again for pointing me in right direction
Ward
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K 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
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives