Excel - How to sum concatenated substrings

user_13493274 0
Viewed 75

I've got a problem I'm trying to work out in excel, and I've only been able to find the solution to the opposite problem.

I've got a list of data in a form similar to the following:

A  | 10
B  | 15
C  | 12
D  | 17

And I want to be able to make a string of any of those strings once and get the sum. The results table would look something like this:

A         |   10
A, B      |   25
A, C      |   22
A, C, D   |   39

In all I've been able to find, I haven't been able to find a way to check if a string contains other strings as substrings, then adds an associated value to a total if that is true.

excel-formula excel
2 Answers

One approach, with SUMPRODUCT and SEARCH.

=SUMPRODUCT(ISNUMBER(SEARCH($A$1:$A$4,E1))*$B$1:$B$4)

enter image description here

Do be aware of pitfalls though when matching substrings; for example, AA in E1 would still return 10 in F1.

user_92458530

In addition to @BigBen, you could try in cell F1:

=SUMPRODUCT(SUMIF(A$1:A$4,FILTERXML("<t><s>"&SUBSTITUTE(E1,", ","</s><s>")&"</s></t>","//s"),B$1:B$4))

That should get rid of the possible false positives.

user_97581940
Related