Search code examples
google-sheetsdropdownmultipleselection

Calculate sum connected to selection from multiple dropdown in Google Sheets

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

Solution 
  • =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:

    enter image description here

    Or

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

    To spill result at once.