Results 1 to 4 of 4

Thread: splitting 2 cell values by comma, then sumproduct

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default splitting 2 cell values by comma, then sumproduct

    Need help.....

    I have 2 different cells having values separated by "comma" (,). result should appear in third cell as sumproduct of these two input cells.

    e.g:-

    5,10 200,100 2000 (=+5*200+10*100)

    1,2,3 50,100,150 700 (=+1*50+2*100+3*150)

    thanking you in advance,

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: splitting 2 cell values by comma, then sumproduct

    Welcome to the MrExcel board!

    This should work for up to 5 numbers per cell. It can be scaled it up if needed.

    Sumproduct

    ABC
    15,10200,1002000
    21,2,350,100,150700

    Spreadsheet Formulas
    CellFormula
    C1=SUMPRODUCT(--(MID(SUBSTITUTE(A1&REPT(",0",5),",",REPT(" ",100)),{1,2,3,4,5}*100-99,100)+0),--(MID(SUBSTITUTE(B1&REPT(",0",5),",",REPT(" ",100)),{1,2,3,4,5}*100-99,100)+0))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: splitting 2 cell values by comma, then sumproduct

    I really appreciate for your hard work. thank you.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: splitting 2 cell values by comma, then sumproduct

    Quote Originally Posted by antenkt View Post
    I really appreciate for your hard work. thank you.
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •