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

#### Hany ali

##### Active Member
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
Code:
``=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

#### Attachments

• 11.5 KB Views: 4
• 9.4 KB Views: 5

#### bosco_yip

##### Excel Ninja
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

Last edited:

#### Hany ali

##### Active Member
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

#### Attachments

• 11 KB Views: 3
• 98 KB Views: 6
Last edited:

#### bosco_yip

##### Excel Ninja
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

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

Last edited: