Forum Discussion

Noxam_Sherei's avatar
Noxam_Sherei
Copper Contributor
Sep 02, 2020

Split cell and Calculate formula

Hi,   Little introduction: I try build complex and clear excel too calculate servals aspect of game economy, for beeter play and learn much more about excel. Including all that causes me a problem...
excel
Rick_Rothstein's avatar
Rick_Rothstein
MVP
Oct 09, 2020

Noxam_Sherei 

 

Assuming the example values you showed are in cells A1:C1, give this formula a try...

 

=SUMPRODUCT(B1*IFERROR(TRIM(MID(SUBSTITUTE("/"&SUBSTITUTE(C1,CHAR(10),"/"),"/",REPT(" ",300)),2*ROW(INDIRECT("1:"&1+LEN(C1)-LEN(SUBSTITUTE(C1,CHAR(10),""))))*300,300)),0))

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 09, 2020

Rick_Rothstein 

That will be 2600*(22+1+2), but not 2600*(22*(value found based on text before 22)+1*(value found based on text before 1)+2*(value found based on text before 2) )

  • Rick_Rothstein's avatar
    Rick_Rothstein
    MVP
    Oct 10, 2020

    SergeiBaklan,

    I did not get the idea from the OP's formula that he was conditioning which numbers to use based on the text before the number. What it looked like to me was that the OP wanted to multiply each number in cell C1 by the value in cell B1 and then add them up. My code is equivalent to that. Based on your interpretation of what the OP wants, what value do you see as the desired result?

     

    EDIT NOTE: Ignore the above response... I reread the OP's message again and see what you mean. My earlier posted formula does not do what I now see as the OP's question. Thanks for noting this for me.

Resources