Converting multi-select values into numerals and adding them up

hooverisbad

Hey folks!

I'm looking to set up a table of values for a stakeholder of mine to manage. The table will look something like this:

Meanwhile, I'll have a separate intake where my target audience will be able to choose multiple options in a list. I'll need these options to basically be converted to numbers on the back end, and then tallied up. Their answers should look like this:

My limitations as I'm aware of them thus far:

• I cannot make a column for each type of answer, as this list will actually be dozens of items long, and may change at an interval I'm currently not privy to.
• I cannot 'hard-code' the answers ((if fish +1) + (if dog +2) + (if cat +3)). The value of these inputs will be subject to change at the whims of my stakeholders, and I'll not be available to go sifting through formulas to update them several times a day/week.
• I CAN make as many helper columns / sheets as I need, as most of this work will be obfuscated by reports and WorkApps.

hooverisbad

After hours of searching, I found this formula on another thread. It feels eerily simple to be working so effectively, but it worked on my silly test-sheet, then it went and worked in the big ol' nasty sheet too.

=SUMIFS({Reference Value }, {Reference Name}, HAS([Dropdown menu]@row, @cell))