Counting Grandchildren Rows based on multiple criteria

Paul Dieterich

Hello, I am trying to count grandchildren rows based on 2 column criteria. My parent row is different task orders and underneath are multiple children(Hospitals) and the children all each have children too (quarters). For each quarter, I need to count the number of grandchildren in that quarter that have a date in my "Date Received" Column.

So my column titles are "task orders" and "Date Received". and my hierarchy looks like this:

Parent: Task Order

Child: Hospital

Grandchildren1: quarter 1

Grandchildren2: quarter 2.

So I want to know the number of Grandchildren in quarter 2 that have a received date. Any help would be great!

Genevieve P.

Hi @Paul Dieterich

We can use the ANCESTORS function to figure out the hierarchy, then based on what level perform different calculations.

For example, in the Quarter 1 level, we can use COUNT(CHILDREN([Date Received]@row)) to count how many children have dates in that column.

For the Hospital level and the Task Order level, we can use SUM(CHILDREN()) to add together the count from each of the Quarter levels.

Here's an example of how I imagine your sheet/structure:

If this is correct, then the formula I have in the Formula column is a Nested IF statement:

FULL FORMULA:

=IF(COUNT(ANCESTORS([Task Orders]@row)) >= 3, "", IF(COUNT(ANCESTORS([Task Orders]@row)) = 2, COUNT(CHILDREN([Date Received]@row)), IF(COUNT(ANCESTORS([Task Orders]@row)) = 1, SUM(CHILDREN()), IF(COUNT(ANCESTORS([Task Orders]@row)) = 0, SUM(CHILDREN())))))

I'll break it down so you know what each IF statement says:

=IF(COUNT(ANCESTORS([Task Orders]@row)) >= 3, "",

If the number of Ancestors for this row is either 3 or more (so, a Quarter parent, Hospital parent, AND a Task Order parent), then be blank. Otherwise...

IF(COUNT(ANCESTORS([Task Orders]@row)) = 2, COUNT(CHILDREN([Date Received]@row))

If the number of Ancestors is 2 (only Hospital parent, AND a Task Order parent), then COUNT the number of children in the Date Received row (it won't count blank cells). Otherwise...

IF(COUNT(ANCESTORS([Task Orders]@row)) = 1, SUM(CHILDREN()),

If the number of Ancestors is 1 (meaning the Parent to this row is only the Task Order parent, no others), then SUM the children in this formula column. So it will SUM the previous COUNT calculation but only for this parent's children. Otherwise...

IF(COUNT(ANCESTORS([Task Orders]@row)) = 0, SUM(CHILDREN())))))

If there are no ancestors, so this is the highest Parent that has all the children (so this is the Task Order row), then SUM the children in this formula column. It won't sum Grandchildren, just the SUM of its own children... so of all the Hospital rows.

This is put in the helper Formula column:

Let me know if this works for you!

Cheers,

Genevieve

Paul Dieterich

Thank you for your comment back! I was actually able to figure it out with =COUNTIFS(DESCENDANTS(Quarters128), "April 1 - June 30, 2020", DESCENDANTS([Date Received]128), ISDATE(@cell))

Genevieve P.

Oh great! I'm glad you were able to find a solution. 🙂

Thanks for posting your answer, too!