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

  • Hollie Green
    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")

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!