How to count data in children rows based on criteria in parent rows

Mary Ayers

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.

Paul Newcome

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))

Paul Newcome

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))

Mary Ayers

Thank you, Paul! That did the trick!

Paul Newcome

Excellent! Happy to help! 👍️