Sum values if cell contains text that isin my source cell (Vlookup cell contains a smultiple values)

[Anthonyinnz]

Greetings

Can someone please help me with the following simplified example.

I have a spreadsheet containing

Code  Amount

101     $10

101     $20

102     $10

103     $20

I want a formulae to sum If the as follows

101                $30

101,103          $50

101,102,103   $60

[#2 · Accepted solution]

Hi @Anthonyinnz 

in E3:

=SUM( ISNUMBER(SEARCH(Table1[Code],[@Codes])) * Table1[Amount] )

AND validate it with [Ctrl]+[Shift]+[Enter] (or equiv. on Mac) if you don't run Excel 2021 or 365

[#3]

Excellent - Thank You
I tried using several different functions without success ... didnt think of the above :o)

[#4]

Glad I could help @Anthonyinnz & Thanks for posting back