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

N

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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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
Nalfen said:
[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]
Click to expand...
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
Nalfen said:
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
Click to expand...

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
Eric W said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

M
  • Question Question
Comparing two columns with comma separated string values (match) and output: matching string/text
Replies
6
Views
384
Fluff
Fluff
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
889
Sektor
Sektor
F
  • Question Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
769
ExceLoki
ExceLoki
H
  • Solved
Time log: Sum a row with both numbers and text
Replies
3
Views
326
Fluff
Fluff
N
  • Solved
Excel Formula to Count a Specific Value in a Range with Cells Containing Multiple Values
2
Replies
13
Views
2K
steve the fish
steve the fish

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

Share this page

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