1

I got a row like this: Current Excel Setup

enter image description here

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

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
Max B
  • 13
  • 2

2 Answers2

1

If T would alway comes after B, you may try the following CSE-entered formula:

To get T:

=SUM(IFERROR(--MID(C2:F2,FIND("T",C2:F2)+1,LEN(C2:F2)),0))

To get B:

=SUM(IFERROR(--MID(C2:F2,FIND("B",C2:F2)+1,IFERROR(FIND("T",C2:F2)-2,100)),0))

enter image description here

Drag to B2.

Surely, someone can come up with something cleaner =)

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    I couldn't think of one. I did try using filterxml as an exercise using your excellent tutorial https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml as a reference and it is possible, but fairly long that way. – Tom Sharpe Jan 04 '21 at 21:18
  • Yeah I tried the same thing but it figured that OP probably got more cells than just the four so I stuck to what I had. Still feels like Im missing something. Even tried `IMSUM()`. @TomSharpe... I just can't do without O365 myself nomore if I see all this hassle =) – JvdV Jan 04 '21 at 21:43
0

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"))

enter image description here

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"))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10