COUNTIF & Multi-Selection Dropdown -- Count multiple entries

MJayMayMJayMay
edited 12/09/19 in Formulas and Functions
12/03/19 Edited 12/09/19
Answered - Pending Review

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 NewcomePaul 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

  • kolfinnakolfinna ✭✭✭✭✭

    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 NewcomePaul 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

  • kolfinnakolfinna ✭✭✭✭✭

    There it is. Thanks so much!

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

    @Jim G Happy to help. 👍️

    thinkspi.com

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

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

    thinkspi.com

Sign In or Register to comment.