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.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    =COUNT(ANCESTORS())


    Then you can use

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

Sign In or Register to comment.