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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!