Formula to count of items in a multi dropdown list

13»

Answers

  • @Paul Newcome Thanks a million 👍👍👍

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Mohammed Alrehaili Happy to help.

    thinkspi.com

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

    thinkspi.com

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

    thinkspi.com

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

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


    CONTAINS("Red", @cell)

    thinkspi.com

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

    thinkspi.com

  • 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,

    Abe

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

    thinkspi.com

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

    thinkspi.com