• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

72146

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

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

72149
 

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:
Top