Sumif (or something similar) using a Multi-Select as a criteria

MCorbin

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 multi-select 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 multi-select and find each matching score to add them together.....

I could do this easily with a single select, but I need a multi-select here (and it all has to be in the same column).

Any Suggestions?

Andrew Stewart

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.

MCorbin

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

Paul Newcome

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