Count specific total number grand children rows and ignore higher rows

tlawrence

Hello All,

I am tracking different sessions being hosted between employees.

The sessions are the rows with the white background. I want to dynamically count the total number of sessions that are happening for row 2 "IMC".

So I need to count the number of children of row 3 "IMC Common Architecture" as well as the number of children of  row 6 "IMC CAD" to come up with a total.

Right now just to make it work, I am using:

=COUNT(CHILDREN([Task Name]3), +COUNT(CHILDREN([Task Name]6)))

Which is somehow returning the number 3, rather than 11.

However, more light gray parent rows, like "IMC RMS" may be added a later date, so I would like to count the sessions under these dynamically and not have to edit my formula in the future.

I can't just count the children of row 2 IMC because then it would include the topic rows like "IMC Common Architecture".

Is this possible?

 

Paul Newcome

Try using a helper column with 

 

=COUNT(ANCESTORS())

 

This will give you a 0 for parent rows, 1 for child rows, 2 for grandchildren rows, etc.

 

You can then use 

 

=COUNTIFS([Helper Column]:[Helper Column], 2)

 

That will count how many rows have 2 ancestors aka grandchildren rows.

tlawrence

Thank you for taking the time to reply Paul, this worked for me!

Paul Newcome

Excellent! Happy to help. 

Andrée Starå

tlawrence and Paul,

I almost always add + 1 in the end because then it will be the same number as in Card View.

Best,

Andrée

Paul Newcome

Good thinking. I have never really had a use for Card View, so I never think about it.

tlawrence

Andree,

Thank you for the suggestion! Could you be a little more specific about what this should look like?

 

Do you mean simply: =COUNTIFS([Helper Column]:[Helper Column], 2) + 1

Wouldn't this throw the numbers off?

Andrée Starå

I was thinking about the hierarchy levels so yes it would not work for the above.

Formula for level: =COUNT(ANCESTORS()) + 1)

Happy Holidays & Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting