Search code examples
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup

How to sum all value based on multiple data input in one cell

I have a sheet like this

A       B     C                            D
Apple   10    Apple Apple Orange Melon     
Orange  30 
Melon   50

I want D1 to show the sum value based on the value in C1 which is 2 apples, oranges, and Melon (10+10+30+50) = 100

Any advice on what to type in column D1? I assume it will be related to sumif?

Solution

  • In addition to the previous formula, this should probably also work:

    =sum(ArrayFormula(iferror(vlookup(split(C1, " "), A1:B, 2, 0))))