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

MCorbin Overachievers Alumni

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
    Andrew Stewart ✭✭✭
    edited 01/10/20

    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
    MCorbin Overachievers Alumni

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!