Riddle me this... CountM on multi-select dropdown but only on parent level row

JBYYC

Hey there Smartsheet community. Long time follower, first time asker.

I have a multi-select dropdown with five options and contributors can select all that apply. I need a total that each option is selected, but I only want to run the count at the parent level.

For this example, we can say my dropdown column is Strategy, and the selection is our strategic pillars which we'll call, Option1, Option2,... Option5. I also have an Ancestor, Children, and Parent helper row, plus one additonal helper named "Top", which appears blank when it's the parent.

I've tried all sorts of variations in a metric sheet that references that column, as well as directly in the sheet. I can usually get COUNTM function to work, but when I try to add in the additional criteria to consider the parent level, it fails.

I've tried every other combination of COUNTIFS and COUNTM and ANCESTOR and PARENT that my novice brain can muster. Any tips?? I can do it in the sheet or in a metric sheet (since there's more than one sheet to count).

Any tips? Am I in over my head

jmyzk_cloudsmart_jp

@JBYYC

Is this what you would like to do?

https://app.smartsheet.com/b/publish?EQBCT=b8164f34bb954b88abecd628a6c2483c

JBYYC

Hi jmyzk_cloudsmart_jp!

Thanks for answering with such a well thought out answer! Unfortunately, that's not quite it. It'd be more like this:

https://app.smartsheet.com/b/publish?EQBCT=918d1df8d3e94c4b933c8572f1849859

I may just ask contributors to only use the dropdown on parent level. It's really only because some people have filled in (sometimes inconsistently) the dropdown on subtasks.

Austin Smith

Test Site - COUNTM @ Parent Level.pdf

@JBYYC

Is this helpful?

formula for counting columns is =IF(ISBLANK([Column2]@row), SUM(CHILDREN([1]@row)), IF(CONTAINS(1, [Column2]@row), 1, 0)) where CHILDREN([?]@row and CONTAINS(? are the only numbers that need changing.

jmyzk_cloudsmart_jp

@JBYYC

I use COUNT(ANCESSTORS()) to find if a row is a PARENT row.

=COUNT(ANCESTORS([Primary Column]@row))

Then, use COLLECT( ) to get a range of Strategy options in the parent row.

Finally, use COUNTIF( ) to count the # of initiatives according to "Strategic alignment" criteria.

=COUNTIF(COLLECT(Strategy:Strategy, ANCESTORS:ANCESTORS, 0), HAS(@cell, [Strategic alignment]@row))

Isn't Option 3 in parent rows 3, not 2?

https://app.smartsheet.com/b/publish?EQBCT=3e7e47f73e264bca862a3505a25ff5e6