How to count data in children rows based on criteria in parent rows
Hello all. I am thinking this would best be done in a Sheet Summary but my brain seems to still be in winter break mode. Hoping someone can point me in the right direction.
My Smartsheet is a list of courses (parent rows including course title and date) with participants (children rows including participant names and a multi-selection field with various consent form types). In this case, the Primary Field is actually the course date (many courses have the same name; the date is the only field unique for each course) – so it is a text field where the first four characters are the year (i.e 2019/01/17).
I need a count of a specific consent form received for participants in a given year. For example, how many participants signed a "human factors" consent form where the Course (ancestor) has “2019” in the Course Date (text) field.
Best Answer
-
You would need to first insert a helper column (that can later be hidden). For this example I will call it "Year". In the Year column, you would use a formula such as...
=LEFT(PARENT([Course Date]@row), 4)
This will pull the 4 left most characters from the parent row (the year).
Then the formula to get your count would look something like this...
=COUNTIFS(Year:Year, @cell = "2019", [Multi-select]:[Multi-select], CONTAINS("human factors", @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You would need to first insert a helper column (that can later be hidden). For this example I will call it "Year". In the Year column, you would use a formula such as...
=LEFT(PARENT([Course Date]@row), 4)
This will pull the 4 left most characters from the parent row (the year).
Then the formula to get your count would look something like this...
=COUNTIFS(Year:Year, @cell = "2019", [Multi-select]:[Multi-select], CONTAINS("human factors", @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, Paul! That did the trick!
-
Excellent! Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!