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

 

[Rick Rothstein]

Assuming the set up you show in Message #5, give this a try...

=SUM(0+TEXTBEFORE(TEXTAFTER(C$1:C$3&","&B5&0,B5)&",",",",1))

 

[kcmuppet]

Assuming the set up you show in Message #5, give this a try...

=SUM(0+TEXTBEFORE(TEXTAFTER(C$1:C$3&","&B5&0,B5)&",",",",1))

Thanks for this idea, Rick.

Trying to understand it from the inside out:

TEXTAFTER(C$1:C$3&","&B5&0,B5)

gives us this list:
2,K0
1,K0
1, GM2,K0

Then this bit...

TEXTBEFORE(list above&",",",",1)

...takes the text before each of those items and any comma that follows them?
I'm struggling a bit with how that works

 

[Rick Rothstein]

Thanks for this idea, Rick.

Trying to understand it from the inside out:

TEXTAFTER(C$1:C$3&","&B5&0,B5)

gives us this list:
2,K0
1,K0
1, GM2,K0

Then this bit...

TEXTBEFORE(list above&",",",",1)

...takes the text before each of those items and any comma that follows them?
I'm struggling a bit with how that works

The comma is concatenated at the end of the list to cover cells without commas so TEXTBEFORE has a comma to find... then TEXTBEFORE returns the text before the **first** comma it finds... this will be the sought after number or 0 which was supplied as part of the TEXTAFTER when the delimiter was not present in the text.

 

[kcmuppet]

The comma is concatenated at the end of the list to cover cells without commas so TEXTBEFORE has a comma to find... then TEXTBEFORE returns the text before the **first** comma is finds... this will be the sought after number or 0 which was supplied as part of the TEXTAFTER when the delimiter was not present in the text.

Thanks! Also Ingenious!