Return a sum value from a column with various entries in each cell

[Trebbs]

I have a spreadsheet that totals values from cells to a summary in another cell.

THe formula in cell M1 is =SUM(IF(ISNUMBER(FIND($L$1,M$9:M$260)),VALUE(LEFT(M$9:M$260,FIND($L$1,M$9:M$260)-1)),0))
the formula will total all the "L" values in the coumn, I wish to add another value into the cell as below

How can I adapt the formula to include the value of 4 in cell M3, this would also need to include all other values in the entire column that are 2 spaces to the left of "B"
Any help appreciated.

 

[DanteAmor]

this would also need to include all other values in the entire column that are 2 spaces to the left of "B"

As long as the pattern is: a digit, space and a letter, you can try this formula:

Dante Amor
	K	L	M	N
1		L	8
2		D	11
3		B	7
4		S	11
5		M	6
6		C	9
7
8		Notes	02-may
9			1 L, 2 D, 3 B, 4 S, 5 M, 6 C
10			3 L, 4 B
11			5 D, 6 S
12			1 M
13			2 D
14			1 S, 2 D
15			3 C, 4 L
16
Hoja7

Cell Formulas
Range	Formula
M1:M6	M1	=SUM(IF(ISNUMBER(SEARCH(L1,$M$9:$M$15)),--TRIM(SUBSTITUTE(SUBSTITUTE(MID($M$9:$M$15,SEARCH(", ? "&L1,", "&$M$9:$M$15),5),L1,""),",",""))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Trebbs]

As long as the pattern is: a digit, space and a letter, you can try this formula:

Dante Amor
	K	L	M	N
1		L	8
2		D	11
3		B	7
4		S	11
5		M	6
6		C	9
7
8		Notes	02-may
9			1 L, 2 D, 3 B, 4 S, 5 M, 6 C
10			3 L, 4 B
11			5 D, 6 S
12			1 M
13			2 D
14			1 S, 2 D
15			3 C, 4 L
16
Hoja7

Cell Formulas
Range	Formula
M1:M6	M1	=SUM(IF(ISNUMBER(SEARCH(L1,$M$9:$M$15)),--TRIM(SUBSTITUTE(SUBSTITUTE(MID($M$9:$M$15,SEARCH(", ? "&L1,", "&$M$9:$M$15),5),L1,""),",",""))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Many thanks for the help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

If I have understood correctly and if you are using your MS 365 then a considerably shorter option might be ..

22 06 18.xlsm
	L	M
1	L	8
2	D	11
3	B	7
4	S	11
5	M	6
6	C	9
7
8	Notes	2/05/2022
9		1 L, 2 D, 3 B, 4 S, 5 M, 6 C
10		3 L, 4 B
11		5 D, 6 S
12		1 M
13		2 D
14		1 S, 2 D
15		3 C, 4 L
Sum

Cell Formulas
Range	Formula
M1:M6	M1	=LET(q,M$9:M$15&"0 "&L1,SUM(--RIGHT(LEFT(q,FIND(L1,q)-1),2)))