Counting selected dropdown elements with Collect function?!?

Andthisjustin

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?

Paul Newcome

You would use a COUNTIFS with a HAS function.

=COUNTIFS({Agents}, HAS(@cell, Agent@row), {Elements}, HAS(@cell, "Robot"))

Paul Newcome

You would use a COUNTIFS with a HAS function.

=COUNTIFS({Agents}, HAS(@cell, Agent@row), {Elements}, HAS(@cell, "Robot"))

Andthisjustin

Thank you Paul, I had not thought about a double HAS to solve this. Can't believe I had not thought of that.

Paul Newcome

Happy to help. 👍️