Reference Sheet with Parent/Child Rows and Multi-Select

Amy Evans

Hello,

Sheet 1 - A sheet that contains parent and children rows with columns containing multi-selects.

Sheet 2- A summary sheet that references Sheet 1. I want to count the children rows of the conditions:

a) Team Red

b) Area only has "West" included.

I think I use COUNTIFS, but not sure how to reference another sheet and only include Children of a specific parent.

Thanks,

Amy

Question.png

Hollie Green

You can create a helper column on your sheet and make it a column formula this will make sure that the name of the parent in the primary column is in a cell on the Child row. You can then reference that specific Team that includes red as a child row name. You will have to create your references.

Helper column formula

=JOIN(ANCESTORS([Primary Column]@row))

=COUNTIFS({Current Areas}, CONTAINS("West", @cell), {Helper column}, ="Team", {Primary Column}, ="Red")

Amy Evans

I created a new column with the parent name on each respective row as a workaround since I'm referencing this from another sheet. I'll hide that new column.