Formula to count of items in a multi dropdown list

Julie HB

Total newbie here, and I'm failing using the terrific, hands-on guidance above. I have a reference sheet with a multi-select dropdown column. [FWIW: in our case, in the platform column, one is selecting which social media platform(s) we posted to.] Here's a sample:

In a separate sheet, I'd like to count how many times a particular dropdown item (e.g. Advocates Facebook, Advocates LinkedIn, etc) is selected, whether on its own or as one of multiple selections in a given row. Instead, it's only counting the number of times the selection is made on its own, like so:

I've tried using the CONTAINS and HAS formulas as written above and keep coming back unparseable or the like, so I'm wondering if anyone has any tips!

Genevieve P.

Hey @Julie HB

Try this:

=COUNTIFS({Platform Column Reference}, HAS(@cell, [Primary Column]@row))

The {reference} would need to be manually created to look specifically at your Platform column. Let me know if this works for you! If not, it would be helpful to see the formula open in the sheet.

Cheers,

Genevieve

Julie HB

Thanks, Genevieve. I appreciate the help! This is how the current formula looks:

This is the formula that only counts the instances when a platform appears on its own, not as one of multiple selections.

Using your formula, I think I need to replace "Platform Column Reference" with the column from my source sheet, and that part seems to work fine.

I am unclear what needs to be replaced after the comma, and with what. In other words, does "@cell" get replaced with a particular reference cell? Does Primary Column get replaced with something else in the brackets? Does "@row" get replaced with a particular row? I know these are very amateur questions, so please feel free to refer me to some type of user guide if I could learn these important details of formula-writing elsewhere! I've scoured the web, but can't seem to figure it on my own.

Many thanks again!

Julie

Genevieve P.

Hi @Julie HB

Thanks for the screen capture, this is great!

What changes are the words {in these} and [in these] based on your specific configuration. @row and @cell are always @row and @cell (here's the documentation - Create efficient formulas with @cell and @row)

In your instance, the Primary Column in this current sheet where you have your Platform data located is called "Primary Column", so that's what you want [in the square] reference brackets. (See: Create a cell or column reference in a formula)

Try copy/pasting exactly this:

=COUNTIFS({2023 Social Media Calendar Range 1}, HAS(@cell, [Primary Column]@row))

I'll break down what it says in each part:

=COUNTIFS({2023 Social Media Calendar Range 1},

Look in the column in the other sheet, the one selected in the cross-sheet reference range.

HAS(@cell,

Check through each individual cell for if it has a specific value, along with other values.

[Primary Column]@row))

In those cells, the value I'm looking for is the one I have typed out in this sheet in the Primary Column. For row 2 this will be "Advocates Facebook". For row three this will be "Advocates Instagram". The @row tells the formula to look in the column to the left (primary) in this current row, so it's dynamic if you drag it down the column.

Here's a formula webinar that you may find helpful as you continue to learn about formulas:

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations

Let me know if this works for you!

Cheers,

Genevieve

Julie HB

Thank you SO much! Works like a charm, and super helpful as I make my way through Smartsheet University. I had a feeling this formula was a ways out in my training, and it really helps to have the numbers now. Appreciate the help!