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

edited 12/09/19 in Archived 2017 Posts

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


Top Level Task 1 (100)

 - Red (3)

 - Amber (27)

 - Green (70)


Top Level Task 2 (150)

 - Red (2)

 - Amber (8)

 - Green (140)



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.





  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭


    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? 


  • Jason Anderson
    edited 10/05/17

    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. 


    Oh, here's my formula 'scratch pad'. 



  • 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



  • 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


This discussion has been closed.