COUNTIF & Multi-Selection Dropdown -- Count multiple entries

MJayMay · 12/03/19

My data input sheet has several columns that are Multi-select dropdowns. For my Metrics summary page, I am struggling with COUNTIF when the user has selected multiple options.

Example of options in the Multi-selection dropdown:

Apple
Pear
Grapes
Banana
Other

I need guidance on how to structure the criteria for when multiple options are chosen, such as Apple & Pear. I want Apple & Pear to be a distinct count, not count all the times Apple or Pear are used individually in the range.

Using the data set shown, I should get the following totals:

Apple - 1 (Not 4, which is the total within the range)
Pear - 1 (Not 3, which is the total within the range)
Grapes - 2
Other - 1
Apple Pear - 2
Apple Banana - 1

Paul Newcome · 12/04/19

Try incorporating the HAS function. It looks for an exact match.

=COUNTIFS([Multi Select Column]:[Multi Select Column], HAS(@cell, "text to search for"))

kolfinna · 09/09/20

Hey there,

I'm trying to use a similar formula within my sheet summary to count the number of Elementary tutors out of a given list. So far I have:

=COUNTM([Age Group of Students]:[Age Group of Students], AND(HAS("Elementary")))

But when I run that, it returns 10 as the answer--however, only 2 rows include that text.

What am I missing?

Paul Newcome · 09/09/20

@kolfinna

COUNTM is designed to count the number of selections made. To count on a specific text you are going to want to use a COUNTIFS similar to...

=COUNTIFS([Age Group of Students]:[Age Group of Students], CONTAINS("Elementary"@cell))

kolfinna · 09/09/20

There it is. Thanks so much!

Paul Newcome · 09/09/20

Happy to help. 👍️

MJayMay · 09/09/20

@Paul Newcome

What syntax do you use when there are multiple selections? From the example above - Apple Pear?

I can find/count single entries easily using CONTAINS, but have yet to find a way to have it identify/count distinct multiple combinations. I need to understand how you reference Apple Pear in the formula below.

= COUNTIFS([Test - Fruit]:[Test - Fruit], CONTAINS("Apple Pear")@cell)

Paul Newcome · 09/09/20

@MJayMay I am not sure exactly what you are looking for. Are you looking for cells that contain both Apple and Pear with other selections possibly made in the same cell, cells that contain either Apple or Pear, or cells that contain strictly Apple and Pear with no other selections made?

MJayMay · 09/09/20

@Paul Newcome - Cells that strictly contain Apple and Pear with no other selections made

Paul Newcome · 09/10/20

@MJayMay

Ok. The delimiter for multi-select columns is a line break which can be written as CHAR(10).

So instead of searching for "Apple Pear", you are going to want to search for "Apple" + CAR(10) + "Pear".

=COUNTIFS([Test - Fruit]:[Test - Fruit], @cell = "Apple" + CAR(10) + "Pear")

Ketin merel · 12/10/20

The team has elected to go to 1h increments in preference to the variable--that makes matters much tidier in relation to using an mcount method to depend all picks within the session availability cell.

Ketin merel · 12/12/20

The group has elected to visit 1h increments in preference to the variable--that makes topics a good deal tidier in relation to using Huntington tutor technique to rely all alternatives within the session availability mobile.

Jim G · 09/09/21

https://community.smartsheet.com/discussion/comment/233516#Comment_233516

Thanks!! It worked for me! 😁👍️

Paul Newcome · 09/09/21

@Jim G Happy to help. 👍️

Pam Dunn · 10/18/21

@Paul Newcome I have a similar situation. I have a dropdown of 11 choices, the person can choose multiple choices in a cell/row. I want to count individually the choices submitted by week. If more than one choice appears in a cell my formula is only counting the one that matches by @row name. =COUNTIFS({Action}, [Action Required]@row, {Wksub}, 39). There could be any combination or number of the choices in Action Required in a cell on the main sheet

Paul Newcome · 10/19/21

@Pam Dunn You need to use the HAS function as outlined above.

COUNTIF & Multi-Selection Dropdown -- Count multiple entries

Answers

Help Article Resources

Categories