Sheet Summary won't count only rows with no ancestors

09/16/20 Edited 09/16/20
Answered - Pending Review

Can't seem to figure this one out. I would like to count rows that have a Status of 'Complete', and are not a child of another row. My formula is this:

=COUNTIFS(Status:Status, "Completed", Status:Status, ANCESTORS() = 0)

What am I missing? It keeps returning just zero. I've tried pointing the second range at the 'Task' column where the parent/child hierarchy is located, but nothing changes. I've also tried just

=COUNTIF(Task:Task, ANCESTORS() = 0)

but that didn't work either.

Appreciate any help. Thanks.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You will need to include a helper column that generates the number of ancestors.


    Then you can use

    =COUNTIFS(Status:Status, "Completed", [Helper Column]:[Helper Column], 0)

Sign In or Register to comment.