Looking for a way to sum multiple text strings within a single cell to a number value

[Nalfen]

Hope someone can help me.

I have single cells containing values separated by commas. Something like this: XFF,ONG,S,HP

I want to be able to lookup occurence of a string and then get a sum of all strings in that cell based on corresponding values found in a table:

DTX	5
XFF	4
ONG	3
S	1
HP	4
INV	1
GENERIC	0

<tbody>
</tbody>

<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody>
</tbody>

Based on the above (XFF,ONG,S,HP) i would want it to return the sum of all those which would be 12

Is that possible?

 

[sandy666]

are you able to use PowerQuery (Get&Transform)?

Items	Value		Sum	Result
DTX	5​		XFF,GENERIC,HP	8​
XFF	4​
ONG	3​
S	1​
HP	4​
INV	1​
GENERIC	0​

Sum	Result
ONG,INV,DTX	9​

 

[Rick Rothstein]

DTX	5
XFF	4
ONG	3
S	1
HP	4
INV	1
GENERIC	0

<tbody>
</tbody>

<tbody>
</tbody>

Is this table complete? I am worried about smaller codes being embedded within longer codes (with the table above, that is not a problem which is why I want to know if the table could contain more codes).

 

[Nalfen]

I cna use Get&Transform, never used it so i would have to look it up but i do have that option.

The table contains 20 rows and 2 columns only

 

[Eric W]

Welcome to the Board.

There may be neater ways, such as PowerQuery or a UDF, but here's one way:

	A	B	C	D	E	F
1	Code	Value		String	Sum
2	DTX	5		XFF,ONG,S,HP	12
3	XFF	4
4	ONG	3
5	S	1
6	HP	4
7	INV	1
8	GENERIC	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula E2 {=SUM(SUMIF($A$2:$A$8,TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),(ROW(INDIRECT("1:10"))-1)*100+1,100)),$B$2:$B$8))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This should work for up to 10 elements in a cell.

 

[sandy666]

I cna use Get&Transform, never used it so i would have to look it up but i do have that option.

The table contains 20 rows and 2 columns only

for future to test

Items	Value		Sum	Result
DTX	5​		ONG,ABC,ZZX,QWERTY	28​
XFF	4​
ONG	3​
S	1​
HP	4​
INV	1​
GENERIC	0​
S	10​
ABC	11​
ZZX	12​
AA	5​
CC	4​
XX	3​
QWERTY	2​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SplitR = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Sum", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sum"),
    Merge = Table.NestedJoin(SplitR,{"Sum"},Table1,{"Items"},"Table1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Table1", {"Value"}, {"Value"}),
    Sum = List.Sum(Expand[Value]),
    Convert = #table(1, {{Sum}}),
    Ren = Table.RenameColumns(Convert,{{"Column1", "Result"}})
in
    Ren[/SIZE]

you can add more "codes" even duplicated

 

[Nalfen]

Welcome to the Board.

Sheet5​

Array Formulas Cell Formula E2 {=SUM(SUMIF($A$2:$A$8,TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),(ROW(INDIRECT("1:10"))-1)*100+1,100)),$B$2:$B$8))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This should work for up to 10 elements in a cell.

Tested this and it works great. I will still lookup the PowerQuery as it looks like a cleaner way to do those things but i appreciate the help.