Formula to count of items in a multi dropdown list



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Mohammed Alrehaili Happy to help.

  • 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
    Paul Newcome ✭✭✭✭✭✭

    @Tom Bradford Try this...

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

  • @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
    Paul Newcome ✭✭✭✭✭✭

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


  • Paul Newcome
    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.
    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
    Paul Newcome ✭✭✭✭✭✭

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

  • Abe B.
    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,


  • Paul Newcome
    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.

  • 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
    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
    S. Medlin
    edited 08/08/22

    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([email protected], @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!