Best way to sum numbers after specific text in a column

[kcmuppet]

What would be the best way to sum the numbers in a column that follow specific text?
In the example below, I want to add the numbers following "K" and those following "GM"

Brooks_Sports_Global_Factory_List_Dec_2022.xlsx
	A	B
1		K2
2		GM3,K1
3		K1, GM2
4
5	Total K	4
6	Total GM	5
Sheet2

 

[alansidman]

Can be achieved with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1.2", Text.Trim, type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Value.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Value.1"}, {{"Sum", each List.Sum([Value.2]), type nullable text}})
in
    #"Grouped Rows"

 

[kcmuppet]

Can be achieved with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1.2", Text.Trim, type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Value.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Value.1"}, {{"Sum", each List.Sum([Value.2]), type nullable text}})
in
    #"Grouped Rows"

Thanks Alan. That's a fascination solution, which I've saved for future reference, but given I'm sharing the sheet with others who can't run PQ I'm looking for a function solution

 

[alansidman]

thanks for the feedback

 

[kcmuppet]

Here's what I came up with.

Brooks_Sports_Global_Factory_List_Dec_2022.xlsx
	A	B	C
1			K2
2			GM3,K1
3			K1, GM2
4
5	Total for	K	4
6	Total for	GM	5
Sheet2

Cell Formulas
Range	Formula
C5	C5	=SUM(IFERROR(MID(C1:C3,SEARCH(B5,C1:C3)+LEN(B5),1)/1,0))
C6	C6	=SUM(IFERROR(MID(C1:C3,SEARCH(B6,C1:C3)+LEN(B6),1)/1,0))

Is there a better way?

 

[RoryA]

Does it need to work with 2010? Also, are they always 1 digit numbers as in your example?

 

[kcmuppet]

Does it need to work with 2010? Also, are they always 1 digit numbers as in your example?

No Office 365 and online. Actually no, they aren't always single digit

 

[RoryA]

Depending on how much data you have, you could try:

=SUM(IFERROR(SUBSTITUTE(TEXTSPLIT(TEXTJOIN(",",TRUE,$C$1:$C$3),",",,TRUE),B5,"")+0,0))

 

[kcmuppet]

Depending on how much data you have, you could try:

=SUM(IFERROR(SUBSTITUTE(TEXTSPLIT(TEXTJOIN(",",TRUE,$C$1:$C$3),",",,TRUE),B5,"")+0,0))

Thanks, if i understand correctly, we join the text in the column adding commas, then split it at each comma, then substitute the text for those being counted with blank, and for every error (where it can't find the text for those being counted, we replace with 0, then sum what's left? Is that about right? Ingenious

I can't work out what the "+0" is for though? Is it to force the text digits to become a number (a bit like my "/1" effort above?

 

[RoryA]

I can't work out what the "+0" is for though? Is it to force the text digits to become a number (a bit like my "/1" effort above?

Yes. The IFERROR handles the situations where the value can't be coerced to a number.