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

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!