Function for Counting Children of Parent Rows

System

This discussion was created from comments split from: Function for Counting Children of Parent Rows.

JamesQ4

Morning @Kelly Moore i have read this thread and was wondering whether you could support these related queries.

1. I am trying to capture the number of children that have a 'Red' RAG from another sheet. I have tried

=COUNTIFS(CHILDREN([{P1 Health}]), "Red") - with no luck.

2. I would then like to see how I could apply this formula to another data set and find all Children with a 'Red' RAG and 'Governance' in a column.

Any help would be appreciated.

Thanks
James

Jgorsich

@JamesQ4 - Not knowing your sheets makes it impossible to give a specific answer, but I suspect you are going to need to incorporate a collect() function or a helper column to make this work.

Countifs presumes that all of your criteria exist on a specific row, so if you are trying to split sheets it isn't going to work. Using collect() allows you to create a hypothetical set of data that meets specific conditions, then do work on those conditions - so, for example, you could collect all tasks names that are red on the other sheet, then count those task names that match the names of the children cells you are looking at.

Alternatively, just pull everything onto the same sheet by adding a helper column that pulls your RAG from the other sheet onto this one, then you can use countifs.

Kelly Moore

Hey @JamesQ4

Unfortunately, children cannot be directly used as cross sheet ranges. The work around is to add a helper column to your source sheet that can be used to find the child rows. You can do this a couple different ways, depending upon the layout of your sheet. If you could provide a screenshot of your source sheet (no sensitive data), I can assist with your helper column. Specifically I'm interested in how many 'levels' of hierarchy you have. Regular rows vs parent/child, Grandparent rows, etc.

Kelly

JamesQ4

Thank you @Kelly Moore & @Jgorsich

Kelly Moore

@JamesQ4

Let me know if you have any questions with your helper column.

Kelly