Formula to count of items in a multi dropdown list

Genevieve P. · 08/18/22

If you're looking in a multi-select column, I would actually recommend using HAS instead of CONTAINS. HAS is built specifically for single values selected with other values in one cell:

=COUNTIFS({Impacted Workgroups}, HAS(@cell, $Label@row))

Then if you have other criteria to filter by, you just need to add the new range, comma, then criteria:

=COUNTIFS({Impacted Workgroups}, HAS(@cell, $Label@row), {Priority Column}, "Priority 1")

If I've misunderstood what you're looking to do, it would be helpful to see a screen capture of the source sheet, where the data comes from.

Cheers,

Genevieve

OFNS BCPS · 10/14/22

Hi I am hoping you can help me out here. I am trying to create a pie chart on a dashboard to display the proportion of technology types that are being serviced. This is a drop-down list with 5 different values. I read it is easiest to count the values individually in the Sheet Summary and then use that to create the chart on the dashboard. If you have a better idea, please let me know.

=(COUNTFS([Technology Type]:[Technology Type], CONTAINS("Computer", @cell))

I am using the above formula but it is coming back as "UNPARSEABLE" Can anyone help? Thanks! @Genevieve P.

Genevieve P. · 10/17/22

Hi @OFNS BCPS

I've responded directly on your other post, here:

https://community.smartsheet.com/discussion/96601/problems-with-using-formula-to-count-items-in-drop-down-list

As a re-cap, try this:

=COUNTIFS([Technology Type]:[Technology Type], HAS(@cell, "Computer"))

Cheers,

Genevieve

OFNS BCPS · 10/19/22

Thank you so much for your help!

K Goforth · 05/19/23

Hello! I'm new at Smartsheet formulas and appreciate any help. I've got a multiselect dropdown list and need to count 1) how many faculty selected each of the individual options. Respondents selected "Faculty" from a column entitled "Job Role."

This formula would only help me count the number of individual options selected from the multiselect dropdown list, correct? For example, if I wanted to determine how many of one specific option was selected across ALL survey respondents:

=COUNTFS(range, CONTAINS("specific text", @cell))

Andrée Starå · 05/19/23

Hi @K Goforth

I hope you're well and safe!

Try something like this.

=COUNTIFS([Job Role]:[Job Role], HAS(@cell, "Faculty"))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

K Goforth · 05/19/23

Thank you! Not quite ... maybe more context will help. I'm really new at this!

In the Job Role column, respondents pick either "Administrator" or "Faculty."

Then in a separate multiselect dropdown column labeled Unit Interaction, they pick which units they interact with on a regular basis. For simplicity's sake, let's say the unit options are "Office A," "Office B," and "Office C."

I'm trying to write formulas that will count out how many Faculty selected "Office A," another formula for "Office B" and so on. The end result is a bar graph on a Smartsheet dashboard that shows which individual offices and how many faculty report interacting with them. Previously in the first draft of this data when we had just a few respondents, I counted offices manually. 😕

Thanks and have a good weekend!

Genevieve P. · 05/22/23

Hey @K Goforth

It sounds like COUNTIFS (with an S, plural) is the right formula for you 🙂

Here's the structure when looking across sheets:

=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

So in your case with 2 multi-select columns, something like this:

=COUNTIFS({Job Role Column}, HAS(@cell, "Faculty"), {Office Column}, HAS(@cell, "Office A"))

Keep in mind if you have "Faculty" and "Office A" typed out in your sheet, you can reference the cells instead of typing in the text, like so:

=COUNTIFS({Job Role Column}, HAS(@cell, [Job Role]@row), {Office Column}, HAS(@cell, [Office Name]@row))

Cheers,

Genevieve

K Goforth · 05/22/23

Thanks Genevieve! I appreciate the help, but I'm still coming up with #unparseable for some reason. Here's a screen shot of my two columns:

And the formula I have right now is:

=COUNTIFS({OHSU Role}, HAS(@cell, [Faculty/Researcher]@row), {RDA Units}, HAS(@cell, [Office of Proposal & Award Management]@row))

Another way I can do this is just set up filters to show the results and I can count those rows. I'm sure there's a formula to do it, but I'm a complete novice (just started with formulas last week with no prior experience), and finding the more advanced ones tough to understand. 🙃

K Goforth · 05/22/23

Update - here are all the items responders can select from for the RDA Units column:

I updated my formula and still get #unparseable. Does it have anything to do with all of the extra parentheses in the RDA Units selection (in bold text)?

=COUNTIFS({OHSU Role}, HAS(@cell, [Faculty/Researcher]@row), {RDA Units}, HAS(@cell, [Office of Proposal & Award Management (OPAM - grants & contracts management, federal grant compliance)]@row))

Genevieve P. · 05/23/23

Hey @K Goforth

Thanks for these screen captures, that's very helpful!

It looks like your first column is either text or single dropdown. In this case, you can simply search for the words in "quotes" directly, or the cell without the HAS - then you'll only need to use HAS for your second, multi-select column. Just make sure what you're searching for is exactly what's in the dropdown column.

I can't quite tell if you have a cell with text or if you want to type the text into the formula.

Lets start with the "typed" version, where you're putting the value right into the formula:

=COUNTIFS({OHSU Role}, "Faculty/Researcher", {RDA Units}, HAS(@cell, "Office of Proposal & Award Management (OPAM - grants & contracts management, federal grant compliance)"))

The cell references [Column]@row only work if you have that same "text" typed into a cell, like in my image above. Does that make sense?

Cheers,

Genevieve

K Goforth · 07/28/23

Thank you - apologies for the late reply.

Kathryn

JonEric Eubanks · 08/15/23

the =COUNTF({Range 1}, HAS(@cell, [Specific Claim]@row)) is the best to count multiple items in a cell

adelmans · 08/23/23

How would I count the number of items selected within a multi-select drop-down and then multiply that amount by let's say 25?

Paul Newcome · 08/23/23

@adelmans You would need a COUNTM function.

=COUNTM(.....) * 25

Formula to count of items in a multi dropdown list

Answers

Categories