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

Nalfen

New Member
Joined
Mar 19, 2019
Messages
3
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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]DTX[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]XFF[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]ONG[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HP[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]INV[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GENERIC[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody> [TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
are you able to use PowerQuery (Get&Transform)?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Items[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#5B9BD5]Sum[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DTX[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td=bgcolor:#DDEBF7]XFF,GENERIC,HP[/td][td=bgcolor:#E2EFDA]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XFF[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ONG[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]S[/td][td]
1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]HP[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]INV[/td][td]
1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]GENERIC[/td][td=bgcolor:#DDEBF7]
0​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Sum[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ONG,INV,DTX[/td][td=bgcolor:#E2EFDA]
9​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]DTX[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]XFF[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]ONG[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HP[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]INV[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GENERIC[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
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).
 
Upvote 0
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
 
Upvote 0
Welcome to the Board.

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

ABCDEF
CodeValueStringSum
DTXXFF,ONG,S,HP
XFF
ONG
S
HP
INV
GENERIC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=SUM(SUMIF($A$2:$A$8,TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),(ROW(INDIRECT("1:10"))-1)*100+1,100)),$B$2:$B$8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




This should work for up to 10 elements in a cell.
 
Upvote 0
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 :)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Items[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#5B9BD5]Sum[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DTX[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td=bgcolor:#DDEBF7]ONG,ABC,ZZX,QWERTY[/td][td=bgcolor:#E2EFDA]
28​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XFF[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ONG[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]S[/td][td]
1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]HP[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]INV[/td][td]
1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]GENERIC[/td][td=bgcolor:#DDEBF7]
0​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]S[/td][td]
10​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC[/td][td=bgcolor:#DDEBF7]
11​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ZZX[/td][td]
12​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]CC[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]XX[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]QWERTY[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]


Code:
[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
 
Last edited:
Upvote 0
Welcome to the Board.

Sheet5


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




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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top