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.