Formula for Sum numbers combined with text in a multiple lines cell

[Hany ali]

Dear all ,I want you help to get sum for cell numbers combined with text in a multiple lines cell
and i have this formula work in cell multiple Lines but without Text .... I Mean Just For Number

=SUMPRODUCT(IFERROR(--MID(SUBSTITUTE(b2,CHAR(10),REPT(" ",30)),{1;30;60;90;120;150},30),0))

if any one can to help me to change this formula to work for Numbers with Text in Column B ....and thanks alot ,with my Best Regards

 

[bosco_yip]

Try

1] A total sum in Column B

In B6, enter an array CSE formula :

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(B2&" "&B3&" "&B4&" "&B5,CHAR(10)," ")," ",REPT(" ",99)),ROW($1:$30)*99-98,99),0))

or, if you have TEXTJOIN function >>

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",,B2:B5),CHAR(10)," ")," ",REPT(" ",99)),ROW($1:$30)*99-98,99),0))

2] Individual sum for each result in Column C

In C2, array CSE formula copied down :

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(B2,CHAR(10)," ")," ",REPT(" ",99)),ROW($1:$30)*99-98,99),0))



Regards

 

[Hany ali]

Well my dear sir, an excellent solution, which is really required. Thank you very much
I apologize very much to you for this other request ... Is it possible to get the total for each item separately?
I mean for

GB

TB

 

[bosco_yip]

Well my dear sir, an excellent solution, which is really required. Thank you very much
I apologize very much to you for this other request ... Is it possible to get the total for each item separately?
I mean for

GB

TB

1] Total breakdown sum

In B7, copied down :

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE("A"&$B$2&"A"&$B$3&"A"&$B$4&"A"&$B$5,A7,REPT(" ",15)),ROW($1:$250),16),),2,15),"[<>];;\0;\0"))

2] Individual breakdown sum

In D2, copied right to E2 and all copied down :

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE("A"&$B2,D$1,REPT(" ",15)),ROW($1:$250),16),),2,15),"[<>];;\0;\0"))

 

[Hany ali]

Well my dear sir, an excellent solution, which is really required. Thank you very much
Well done, really you are a genius professor