Riddle me this... CountM on multi-select dropdown but only on parent level row
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
Answers
-
-
Thanks for answering with such a well thought out answer! Unfortunately, that's not quite it. It'd be more like this:
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.
-
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.
-
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?
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!