#### 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

Options
edited 12/09/19

Hi everyone

i.e.

- Red (3)

- Amber (27)

- Green (70)

- 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

• ✭✭✭✭✭✭
Options

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

• edited 10/05/17
Options

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

• Options

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

• Options

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:

Then a nested if statement ...

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

This discussion has been closed.