Parent-Child Count distinct children under a specific date and type parent

userdetails

Hi everyone, I'm trying to establish a parent shild structure that will help me count total number of distinct children under a parent when two conditions of parent are met. Below is an example grid. I'm loking for a formula that will count the number of disticnt children that are listed under parent that are Blue and belongs to year 2025. Any luck?

Thanks for your time in advance!

KPH

Hi

I don't know exactly what you want, but have some ideas that should get you started. First thing would be to add a helper column to identify a row's parent. You can use =PARENT([Task Name]@row), make it a column formula and hide the column.

Then you can use a COUNTIFS function to count the rows that meet your criteria. For example, this:

=COUNTIFS(Type:Type, "Blue", Date:Date, YEAR(@cell ) = 2025, Parent:Parent, [Task Name]@row)

Will count where the type is Blue, the date is in 2025, and the parent is the task on the current row. An example would be this:

KPH

Sorry - pasting into this forum can break the comment.

What I don't understand from the question, and have not done is consider whether you want to count children and grandchildren, what you want to use for distinct, or what you mean by conditions of parents. This formula looks at the values in the child rows against the fixed values - blue and 2025 and counts all children but not grandchildren and does not exclude any duplicates. Hopefully that can get you started. If you need more, just ask.

Have fun!

userdetails

Thanks for your help! Looks like I didn't give enough info and created confusion, sorry.. I'm looking for the count of distinct grandchildren that are under children, which are from year 2025 and Blue type. Hope this clarifies. Appreciate the help!

Genevieve P.

@KPH

Hi hi … just noticed your comment about "breaking" the forum - can you clarify where you copy/pasted from? Looking to replicate and report this! Thanks 🙂

KPH

Hi @userdetails

You can edit the formula in the parent column to return the Grandparent using a formula like this:

=IF(COUNT(ANCESTORS([Task Name]@row)) > 1, INDEX(ANCESTORS([Task Name]@row), 1), "")

This counts the number of ancestors the row has, if this is more than 1, it returns the Task Name for the 1st ancestor. Like this:

You can then use that column in the count:

=COUNTIFS(Type:Type, "Blue", Date:Date, YEAR(@cell ) = 2025, [Grand Parent]:[Grand Parent], [Task Name]@row)

Hope that helps.

KPH

Hi @Genevieve

The issue only occurs when I use a mac. When I copy an image (command C) and paste into the comment (command V), it will on occasion go doolally. The comment window goes blank (all my previous text and images disappear - terrifying). Saving the draft, refreshing the screen, etc. does nothing. However, if I post the comment, what was there at the time it went into a tizz, appears. If I try to edit the comment, it is blank again. It has been doing this for a few months and is possibly caused by not copying the image properly before pasting - as if command v without something in the clipboard is causing the comment to get upset, rather than to paste nothing.