Formula to count of items in a multi dropdown list

Mohammed Alrehaili

@Paul Newcome Thanks a million 👍👍👍

Paul Newcome

@Mohammed Alrehaili Happy to help.

Tom Bradford

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?

Paul Newcome

@Tom Bradford Try this...

=COUNTIFS([Column2]:[Column2], HAS(@cell, "maybe"))

Tom Bradford

@Paul Newcome

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))

Paul Newcome

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

Allison Huerta

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!

Paul Newcome

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

CONTAINS("Red", @cell)

Abe B.

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,

Paul Newcome

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

Abe B.

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

Paul Newcome

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.

Danielle C

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!

Paul Newcome

@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.

S. Medlin

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 multi-selection drop-down 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 1-4.

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 multi-selection 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}.