COUNTIF DESCENDANTS of the highest ancestor of the row being queried

TR

Data sits at the third structural level of a data table.

I want a column with a value on each row to tell me how many descendants there are from the highest ancestor of that row (i.e. within that structure)

Basically, the current row is 1 of how many descendants?

So all third-level rows will show the same count value within the same structure

As an example, the value is returning correctly for each row when there's an absolute reference for the highest ancestor cell in that particular structure (Role$1348):

=COUNTIF(DESCENDANTS([Role]$1348), [Role]@row)

But I need each structure to know its own highest ancestor cell for the Role column - I don't want to be creating absolute refs for each structure.

Thanks

Genevieve P.

Hi TR,

There's likely a more succinct way to do this, but what I would do is have a helper column pulling in the Ancestor Names down one column, then use a COUNTIF function on that name column, like so:

My Parent Formula:

=IF(COUNT(ANCESTORS()) = 0, "Top Level", JOIN(ANCESTORS([Task Name]@row), ", "))

My Formula to count:

=COUNTIF(Parent:Parent, Parent@row)

Let me know if this is what you were looking to do or if I misunderstood!

Cheers,

Genevieve

Genevieve P.

Hi TR,

There's likely a more succinct way to do this, but what I would do is have a helper column pulling in the Ancestor Names down one column, then use a COUNTIF function on that name column, like so:

My Parent Formula:

=IF(COUNT(ANCESTORS()) = 0, "Top Level", JOIN(ANCESTORS([Task Name]@row), ", "))

My Formula to count:

=COUNTIF(Parent:Parent, Parent@row)

Let me know if this is what you were looking to do or if I misunderstood!

Cheers,

Genevieve