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
Check out the Formula Handbook template!