COUNTIF & Multi-Selection Dropdown -- Count multiple entries

MJayMay
MJayMay
edited 12/09/19 in Formulas and Functions

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

Multi-Select.JPG

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/05/20

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

     

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

    thinkspi.com

  • kolfinna
    kolfinna ✭✭✭✭✭✭

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

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

    thinkspi.com

  • kolfinna
    kolfinna ✭✭✭✭✭✭

    There it is. Thanks so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

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

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

    thinkspi.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jim G Happy to help. 👍️

    thinkspi.com

  • Pam Dunn
    Pam Dunn ✭✭✭✭✭

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

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

    thinkspi.com