# Counting Grandchildren Rows based on multiple criteria

Options

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:

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!

• Employee
Options

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:

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

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 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 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 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

October 8 - 10, Seattle, WA | Register now

• Options

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))

• Employee
Options

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