Reference Sheet with Parent/Child Rows and Multi-Select
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
Answers
-
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")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!