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

jehup

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.

Kelly Moore

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

Kelly Moore

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

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

@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

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

@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

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

@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

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