Counting number of tasks, by top level then by RAG status

info25176

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

 

Richard Rymill SBP

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 

Jason Anderson

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 Phase

Thinking 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

 

SmSulli

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

 

info25176

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