Flag Parent Row if Child Task Meets Condition

katie.dickinson

I am looking for a way to count the children of a parent row, but only if the children have a particular attribute that is true. I'll then use that count to set up an CountIF to flag the parent row when the count is greater than one.

The way my sheet is set up, I have parent rows and child rows. Milestone tasks are denoted by a checkbox where the checkbox is checked if the task is a milestone. I want to create a flag that indicates the parent row has at least 1 child row where the child is a milestone.

So put another way: Flag the parent row if any of the children are milestones.

Any thoughts? I can only seem to get as far as counting the children of the parent row regardless of whether they're milestones or not.

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), @cell = 1) > 0, 1)

katie.dickinson

Thank you - Paul!

I ended up adapting the formula to this but you put on the track that I needed - I wasn't sure how to establish the COUNTIFS range correctly when I wanted to reference a specific set of children/descendants:

=IF(COUNTIFS(DESCENDANTS(Milestone@row), Milestone@row = 1) > 0, 1)

• I chose to use DESCENDANTS to handle situations where I had a grandparent row; I still wanted it flagged.
• I needed to identify the Milestone column specifically in the DESCENDANTS part - as in DESCENDANTS(Milestone@row) vs. DESCENDANTS() - because without declaring that milestone@row, the formula was looking to see if the children had the box checked that I was trying to check with the formula I was creating despite the criteria in the COUNTIFS.

My ultimate use case for this was to create a milestone report for a project plan. The parent/ancestor rows provide context about which phase of the project the milestones are a part of it. Unlike with sheet filters where you can just say "include parent rows", reports won't do that. So I needed a flag to indicate when the row was a parent/ancestor row AND the child was a milestone because I didn't want to pull in a header row if it didn't have a milestone task under it.

This solved it!

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), @cell = 1) > 0, 1)

katie.dickinson

Thank you - Paul!

I ended up adapting the formula to this but you put on the track that I needed - I wasn't sure how to establish the COUNTIFS range correctly when I wanted to reference a specific set of children/descendants:

=IF(COUNTIFS(DESCENDANTS(Milestone@row), Milestone@row = 1) > 0, 1)

• I chose to use DESCENDANTS to handle situations where I had a grandparent row; I still wanted it flagged.
• I needed to identify the Milestone column specifically in the DESCENDANTS part - as in DESCENDANTS(Milestone@row) vs. DESCENDANTS() - because without declaring that milestone@row, the formula was looking to see if the children had the box checked that I was trying to check with the formula I was creating despite the criteria in the COUNTIFS.

My ultimate use case for this was to create a milestone report for a project plan. The parent/ancestor rows provide context about which phase of the project the milestones are a part of it. Unlike with sheet filters where you can just say "include parent rows", reports won't do that. So I needed a flag to indicate when the row was a parent/ancestor row AND the child was a milestone because I didn't want to pull in a header row if it didn't have a milestone task under it.

This solved it!