# COUNTIF DESCENDANTS of the highest ancestor of the row being queried

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

• 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

• 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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!