How to count one value or combo of values in a column of multiple values

Mike Beam

Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did not choose a specified option. We can count all instances when one particular option was chosen using the following formula, but we cannot figure out how to include multiple terms or exclude terms.

=COUNTIFS([columnName]:[columnName], HAS(@cell, "optionName"))

We need help with 2 goals:

Goal 1: Formula that counts how many instances of specified option but only if it was the only choice

If we have five options (Option 1, Option 2, Option 2, Option 4, Option 5), we want to know how many people chose Option 1 *but did not choose any other option*, how many chose Option 2 *but did not choose any other option*, and so on. We are trying to isolate how many times a specified option was chosen as the only option.

Goal 2: Formula that counts how many instances of multiple specified options

We want to know how to count how many times a user chose a specified combination of options. In other words, when a user chose an Option AND another specified Option. For example, if a user chose Option 1 and Option 3, we want to count that. *We need to make sure we aren't accidentally counting all times Option 1 or Option 3 were chosen--only count the times when they are both chosen in the same cell.

Nick Korna

For Goal 1:

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 1, Dropdown:Dropdown, HAS(@cell, "Option Name"))

For Goal 2 (with 2 options):

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 2, Dropdown:Dropdown, AND(HAS(@cell, "Option Name 1"), HAS(@cell, "Option Name 2")))

If your options are numbers, you can leave out the quotation marks.

If you are wanting to have more than 2 options, increase the COUNTM value and add the extra HAS(@cell, "Option Name X") within the AND function.

Hope this helps, but I've misunderstood something or have any problems/questions then just post them up! 🙂

Nick Korna

For Goal 1:

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 1, Dropdown:Dropdown, HAS(@cell, "Option Name"))

For Goal 2 (with 2 options):

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 2, Dropdown:Dropdown, AND(HAS(@cell, "Option Name 1"), HAS(@cell, "Option Name 2")))

If your options are numbers, you can leave out the quotation marks.

If you are wanting to have more than 2 options, increase the COUNTM value and add the extra HAS(@cell, "Option Name X") within the AND function.

Hope this helps, but I've misunderstood something or have any problems/questions then just post them up! 🙂

Mike Beam

@Nick Korna Super helpful! Thanks so much! One follow-up for the Goal 2 solution. How would you go about excluding other options at the same time?

For example, I used that formula to search for two options. It found all instances of those two options being selected together. But it also included instances where other options were *also* chosen in addition to the two specified. How can I exclude counting those instances?

To be more specific:

I want to count this: Option 1 and Option 2 only

Example Cell #1: Option 1, Option 2

Example Cell #2: Option 1, Option 2, Option 3, Option 4

I want Cell #1 to be counted but not Cell #2. How would I go about excluding the counting of when other options are also selected?

Nick Korna

It should already do this - the COUNTM portion is making sure the cell only has the correct number of options (in this case 2).

In your example, Cell #1 would be counted, but Cell #2 would not, as its COUNTM value is 4, not 2.

In the below example:

Both columns are looking for 2 & 3 as the options, With 2nd column has the COUNTM portion in and the 3rd does not - hence the first/last dropdown values are counted in the latter case.

Mike Beam

@Nick Korna Thank you! I stand corrected. I was making an error when I was doing my manual double-check. The formula you provided works as you said. Thanks again!

Nick Korna

No problem, happy to have helped. 🙂