This is tricky to describe, but should make more sense with an example. I have a simple table with two columns - a name, and a number value. For example (Town, Population).

My input values are a single cell that looks like NY, LA or LA, Boston, NY

Importantly, these inputs can be of arbitrary length, they might list two cities, they might list 50. LA, Boston, NY, A, B, C, Foo, Bar...

I'd like to return the sum of the values related to each of the delimited cities.

So NY, LA returns 25 (NY = 10, LA = 15; the sum of 10 and 15 is 25) and LA, Boston, NY returns 45.

I'm guessing some combination of index/match and a sum function, but I can't quite get index/match to grab multiple values simultaneously from a single input cell and then manipulate those values to a single output cell.

LEFT/RIGHT/MID etc. I don't think work, due to the arbitrary length of the input string.

Any help at all would be appreciated!

For the visual learners: see an example on imgur!

CodePudding user response：

With Microsoft-365 you can use below formula.

=SUM(SUMIFS(B2:B9,A2:A9,FILTERXML("<t><s>"&SUBSTITUTE(E2,",","</s><s>")&"</s></t>","//s")))

CodePudding user response：

Notice the meaning of the comma and use it for exact matching：

=SUMPRODUCT((ISNUMBER(SEARCH(","&A2:A4&",",",NY,LA,"))*B2:B4))