How can I count two drop down options in a single cell?

I was able to count single drop down options but I was unable to figure out how to add two drop down options, here is the formula I am using: =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, HAS(@cell, "Schedule")) (This is to count only "Schedule" items on the Column. I have several rows with two options, here is one example" Schedule Technical.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @jehup

    Thanks for the screenshot - in your original post I thought you meant you had both "Schedule" and "Schedule Technical" as possible responses.

    Building on @sharkasits response, to limit the counts to where multiple responses are in the cell

    =COUNTIFS({WHS Hot Issue Tracker_NO Range 3}, COUNTM(@cell)>1, {WHS Hot Issue Tracker_NO Range 3},OR(HAS(@cell, "Schedule"), HAS(@cell, "Technical")).

    The above counts when there are multiple responses in a cell and EITHER Schedule or Technical is in that cell. @sharkasits' AND formula above will get you the counts when both are simultaneously in the cell.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/11/23

    Hey @jehup

    When you are trying to count multiple instances within the same cell, one has to begin to treat the cell, or in your case the range, as one long text string. The length of the long text string can be determined, as well as the length of the string if all the instances of that word are removed. Finally, when that difference is divided by the length of the single word, you will see how many multiples of the word originally existed. It's a long explanation to tell what the formula is doing.

    =(LEN(JOIN({WHS Hot Issue Tracker_NO Range 3})) - LEN(SUBSTITUTE(JOIN({WHS Hot Issue Tracker_NO Range 3}), "Schedule", ""))) / LEN("Schedule")

    Does this work for you?

    Kelly

  • jehup
    jehup ✭✭
    edited 01/12/23

    Hi @Kelly Moore

    It does work but I want to count only the row items that have two more and the formula is counting single and dual items per row (all Schedule items). Is there a possibility to count two items at the time? E.g. Schedule and technical. Below is a picture of the rows I want to count:


    I want Smartsheet to count all items with Compliance and Schedule and Technical and Schedule etc. Thanks

  • sharkasits
    sharkasits ✭✭✭✭

    @jehup I'm not sure exactly what you're trying to do, so I'll give a few options:

    If you are trying to count the rows where more than 1 item has been selected:

     =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, COUNTM(@cell)>1)

    If you are trying to count the rows where 2 specific items have been selected:

    =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "Schedule"), HAS(@cell, "Technical"))

    If you are trying to count the rows where either one or both of 2 specific items have been selected:

    =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, OR(HAS(@cell, "Schedule"), HAS(@cell, "Technical"))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @jehup

    Thanks for the screenshot - in your original post I thought you meant you had both "Schedule" and "Schedule Technical" as possible responses.

    Building on @sharkasits response, to limit the counts to where multiple responses are in the cell

    =COUNTIFS({WHS Hot Issue Tracker_NO Range 3}, COUNTM(@cell)>1, {WHS Hot Issue Tracker_NO Range 3},OR(HAS(@cell, "Schedule"), HAS(@cell, "Technical")).

    The above counts when there are multiple responses in a cell and EITHER Schedule or Technical is in that cell. @sharkasits' AND formula above will get you the counts when both are simultaneously in the cell.

    Kelly

  • jehup
    jehup ✭✭

    @sharkasits @Kelly Moore Thank you very much for the answers ! they worked ! One last question (Sorry for asking too much), Since I can count now how many times "Schedule" and Technical are on the same row, how can I count the number of times "Schedule" is alone in a row? I am getting "Schedule" 11 times but is counting 2 times the row where "Schedule" and "technical" are present? So, I was expecting to see 9 "Schedule" and 2 "Schedule and Technical" Thanks.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @jehup

    =COUNTIFS({WHS Hot Issue Tracker_NO Range 3}, HAS(@cell, "Schedule"))

    The HAS (or, alternatively CONTAINS function) is the correct function to use when one is trying to find a value within a multiselect column.

    Kelly

  • jehup
    jehup ✭✭

    @Kelly Moore Thanks, what I want to do is just count the number of single "Schedule" rows, and avoid counting Schedule twice on the rows where we have two options. If you see below, there are only 7 "Schedule" and all other rows have two options. I am using =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, HAS(@cell, "Schedule")) to count the number of "Schedule" and is counting 13. Is there a way to filter "Schedule" only? Thanks.



  • jehup
    jehup ✭✭

    @Kelly Moore I was able to figure it out ! I use contains, =COUNTIF({WHS Hot Issue Tracker_NO Range 3}, CONTAINS(@cell, "Schedule")) and is now only counting the number of "Schedule" items. See below now the total of "Schedule" items is 7. Thank you very much for the help !


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!