Counting selected dropdown elements with Collect function?!?

Andthisjustin ✭✭
edited 06/07/23 in Formulas and Functions

Hey all, first time caller, long time listener.

I have a sheet (Sheet 1) that has a drop down column, the user can select any or all of 26 elements. In the same sheet I have a column titled agents with 15 agents in a drop down multiselect cell. This sheet has over 900 rows on data.

In a metric sheet (where I am aggregating the data), I have one of the 15 agent names in a cell (Agent James Bond), then another column with the 26 elements (lets just assume ROBOT, MONKEY, BUTLER and so on) expanded into individual cells downward. I would like to get the count if any of the items are selected, with the corresponding agent.

I tried =COUNTM(COLLECT({26Elements}, {Agents}, @cell = Agent@row, FIND("Robot", @cell), 0))

But alas, it is not correct. I have wrecked my brain on this logic for a while now, and believe I am missing something easy, or I am over complicating things here.

Any thoughts?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!