2

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Joshua Wagner
  • 23
  • 2

2 Answers2

3

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Dang - that formula is much simpler than I had imagined. Nice! – urdearboy May 07 '20 at 18:55
  • Yeah... it might have some false positives though. – BigBen May 07 '20 at 18:56
  • Yea, I believe this assumes Column A will be single string character right? So, if there was a value for `AA` in `Column A` the associated value would be incorrectly added to `A` – urdearboy May 07 '20 at 18:57
  • Wow, thanks! I've been searching for an hour or so and I could only find something to sum if the string I was looking for was a substring of a longer string. – Joshua Wagner May 07 '20 at 18:58
  • 3
    @urdearboy - not necessarily a single string, but just that one row's text cannot be also part of another row's text. So "foo" and "foobar", "car" and "care", wouldn't work, and "A" and "AA" wouldn't also. Problems of substrings. – BigBen May 07 '20 at 18:59
  • @urdearboy - all credit goes to Scott Craner in [this answer](https://stackoverflow.com/questions/53109551/is-there-an-excel-formula-that-will-search-a-list-and-highlight-text-containing). – BigBen May 07 '20 at 19:01
  • I see what you mean. I think this is a limitation I can deal with, my data shouldn't get so complicated that that would have an effect, but it'll be good to keep in mind. – Joshua Wagner May 07 '20 at 19:02
3

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70