Formula to count of items in a multi dropdown list
Answers

@Paul Newcome Thanks a million 👍👍👍


Within a sheet, in column 2 I have a drop down (multi select) option with the choices 'yes' and 'maybe' available.
I would like to understand the count of instances where maybe has appeared in the column.
In a separate cell I have entered the following formula;
=COUNTIFS([Column2]:[Column2], HAS([Column2]@row, "maybe"))
I have also tried, =COUNTIFS([Column2]:[Column2], CONTAINS("maybe", [Column2]@row))
In both instances, though the '[Column2]@row' cell has the 'maybe' option as a value, the formula returns 0.
Am I doing something wrong that you can tell from my description?

@Tom Bradford Try this...
=COUNTIFS([Column2]:[Column2], HAS(@cell, "maybe"))

Hi Paul,
Thanks for taking the time to respond, unfortunately, still not evaluating when I have entered;
=COUNTIFS([Column2]:[Column2], CONTAINS("maybe", [Column2]1))
It's odd, as to test, I have entered the below in a separate cell, and the result of '1' is produced when the word 'maybe' is encountered
=IF(HAS([Column2]@row, "maybe"), 1, IF(HAS([Column2]@row, "yes"), 2, 0))

@Tom Bradford Try my formula exactly as is where the "range to evaluate" portion inside of the HAS function is @cell.

I have tried the formulas above, and nothing seems to work.
First formula  result is 0 (should be 3):
Second formula ... using COUNTM  result is 11? weird:
Third formula  result is 0 (should be 3)
Am I missing something in these examples?
Thanks!

@Allison Huerta You need to use "@cell" in place of the range within the CONTAINS or HAS function.
CONTAINS("Red", @cell)

Hi folks,
I have a question very similar to the first one from (lcamacho19911), the only difference is that I only need to count the results in ANCESTORS= Parent= Level 1 rows. Given that I already have a column for Levels. Would appreciate any help.
Thanks in advance,

@Abe B. Are you able to provide a screenshot for reference?

Hi Paul,
Sorry just seen this message as I haven't received any notifications.
I honestly can't provide screenshots as its all business related data. But things gotten even harder as far as what I am asked to do and what I am trying to accomplish here. Not sure if 1:1 call is possible.
Thanks again Paul,
Abe

If you can provide a screenshot with the sensitive data blocked out and only leave the ancestors column visible, we should be able to work with that.

I have a similar question I think ( am a bit of a smart sheet newbie here! ) I have a drop down list, which you can select as many as applicable out of 10 responses but I want to know which answer appears the most only  any help would be greatly appreciated!

@Danielle C You would need to get the count for each and then compare them. There are a number of ways to do this, but my suggestion would be listing out each option on a separate sheet and using a COUNTIFS with cross sheet references.

Hi, I'm trying to create a report on a sheet where it feeds data from a master project tracker. The master sheet has a multiselection dropdown column where 1 or more workgroups can be selected. The master sheet also has a project priority level column to denote if it is a Priority 14.
I've used the below formula to count the number of projects that impact individual workgroups, even when more than 1 workgroup is selected in the multiselection cell and it seems to work well.
=COUNTIFS({Impacted Workgroups}, CONTAINS($Label@row, @cell))
My question is I now need to go across the row and identify how many of that workgroup's projects are Priority 1, Priority, 2...etc.  these would be in the light grey cells and the data feed comes from another sheet like the total does in the formula above {Impacted Workgroups}.
Help Article Resources
Categories
Check out the Formula Handbook template!