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))
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))
-
Thank you, Paul! That did the trick!
-
Excellent! Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!