Excel - Summing values based adjacent letter in a table array

Question

I got a row like this: Current Excel Setup

I got a formula to sum the T and B values:

T: =SUM(IFERROR(SUBSTITUTE(C2:G2,"T","")*1,0))
B: =SUM(IFERROR(SUBSTITUTE(C2:G2,"B","")*1,0))

This works when a cell only contains values like B5 or T3 etc. I would also like it to sum the B's and T's where a cell contains values like B2T3

So example:

B5
B5
B2T3
T3

Should show me B = 12 and T = 6

In the attached screenshot, I cannot get the formulat to recognise and pickup the B and T values from cell F2

Answer 1

In D2, formula copied down :

=SUMPRODUCT(0+TEXT(LEFT(TEXT(MID($A$2:$A$5&"@",FIND(C2,$A$2:$A$5&C2)+1,COLUMN(A:I)),),COLUMN(A:I)-1),"[<>];;;\0"))

Edit :

If the data put in horizontally, just change this formula part from COLUMN(A:I) to ROW($1:$9)

It is a robust formula, criteria & data can be variable, and sum with decimal point numbers

In A2, formula copied right to B2 and all copied down :

=SUMPRODUCT(0+TEXT(LEFT(TEXT(MID($C2:$F2&"@",FIND(A$1,$C2:$F2&A$1)+1,ROW($1:$9)),),ROW($1:$9)-1),"[<>];;;\0"))