Sumif (or something similar) using a MultiSelect as a criteria
I am trying to find a way to allow users to select multiple criteria, where each criteria has a score. And once selected, have a column add up the scores for all criteria selected.
For Example:
Criteria Score
Cat 10
Dog 9
Llama 8
Zebra 7
If the animals are in a multiselect drop down, and the user selects Cat, Llama, and Zebra, I want to be able to create a calculation that find the scores for each and adds them up  coming up with 25. (10+8+7)
I thought about having a supporting sheet that lists the criteria and score, but I can't figure out how to start with a multiselect and find each matching score to add them together.....
I could do this easily with a single select, but I need a multiselect here (and it all has to be in the same column).
Any Suggestions?
Answers

Hi MCorbin,
There are many ways, one that works (but not scalable if you have a huge number of criteria, is this the case?) is to use countifs and contains to get 0 if the criteria is absent, 1 if it is present, multiply by the criteria weights and add up as follows:
=COUNTIFS([Criteria Score]@row, CONTAINS("Cat", @cell)) * 10 + COUNTIFS([Criteria Score]@row, CONTAINS("Dog", @cell)) * 9 + COUNTIFS([Criteria Score]@row, CONTAINS("Llama", @cell)) * 8 + COUNTIFS([Criteria Score]@row, CONTAINS("Zebra", @cell)) * 7
Actually, I just realised you do not need the COUNTIFS in this case, just the Contains, but I was deriving from a previous solution that was collecting a value across a range of cells. Still works if the range is just one cell 😉, so why not reuse it.

Thanks  that would work. I shouldn't have more than 20 criteria in any column (a pain to set up at first, but once it's done, it's done).

You can actually simplify this to just add together IF statements instead of using the COUNTIFS functions and multiplication since you are working on a row by row basis.
=IF(CONTAINS("Cat", [Criteria Score]@row), 10) + IF(CONTAINS("Dog", [Criteria Score]@row), 9) +IF(CONTAINS("Llama", [Criteria Score]@row), 8) .......................................................
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!