Calculate sum connected to selection from multiple dropdown in Google Sheets

Question

In google sheets I need to create a formula, that will calculate the sum based on the selection from the multiple selection drop down list.

Lets assume that we have sheets "names" and "values" in Sheet "names" we have column A with multiple selection dropdown lists A a,b a,b,c a,c b,c

On sheet "values" I have that a=1, b=2, c=10 ... So in sheet "names" I need to create column B, that will automatically calculate the sum of selected in column A letters.

A.       B
a,b.    1+2=2
a,b,c.  1+2+10=13
a,c.    1+10=11
b,c.    2+10=12

etc

Answer 1

=arrayformula(SUM(IFERROR(VLOOKUP(SPLIT(A1,","),$D$1:$E$3,2,FALSE),0)))

And drag it down. This assumes that all values in A are separated by "," and no other characters and also assumes 0 if letter is not found. Result:

Or

=byrow(A1:A6,lambda(z,arrayformula(SUM(IFERROR(VLOOKUP(SPLIT(z,","),$D$1:$E$3,2,FALSE),0)))))

To spill result at once.

Answer 2

You can also do it with REGEXMATCH and FILTER, despite its delimiter:

=SUM(FILTER(values!B:B,INDEX(REGEXMATCH(A1,values!A:A)),values!A:A<>""))

Also as an array:

=BYROW(A:A,LAMBDA(each,SUM(FILTER(values!B:B,INDEX(REGEXMATCH(each,values!A:A)),values!A:A<>""))))