Creating a formula for metrics sheet with multi-selection dropdown

stephenperez

Hi all,

I am creating a metrics sheet, and am having trouble creating a formula to count how many cells of a specific variable (call it "Zone") has multiple selections. I've already counted the ones where only one zone is selected but, but am missing the counts for those remaining with multiple zones selected (can call these zones A, B, C, Z). This is part of a larger formula that I have accounted for, but am only missing this portion of it. I have read on discussion posts and elsewhere online about Contains, Has, CountM formulas, but have tried variations of each of these with no success. Any suggestions would be greatly appreciated. Thank you!

Paul Newcome

You would need a HAS function.

=COUNTIFS({Multi-Select Dropdown}, HAS(@cell, "A"))

stephenperez

Thank you for your prompt reply, Paul. While this worked, I need to only count where there is >1 value in a cell, so if it is any combination of A, B, C, Z. Rather than just unique values.

https://community.smartsheet.com/discussion/comment/488700#Comment_488700

Paul Newcome

Oh. In that case it would be

=COUNTIFS({Multi-Select Dropdown}, COUNTM(@cell) > 1)

stephenperez

https://community.smartsheet.com/discussion/comment/488709#Comment_488709

Thanks Paul. Here is my current code:

=COUNTIFS({DCP Borough & Zone or DYFJ Program}, COUNTM(@cell ) > 1, {Event}, [SKIP - Individual Coaching]$1, {Event Date}, @cell >= DATE(2024, 7, 1), {Event Date}, @cell <= DATE(2025, 6, 30), {QA Status}, OR(@cell = "", AND(@cell <> "Canceled", @cell <> "Cancelled")))

While this returns any event that has >1 value for the DCP Borough category, I only want it to return events with any combinations of Zones "A, B, C, Z," as there are other zones that I want to filter out.

Can I combine the CountM function while also filtering for those zones in the same variable?