# Thread: splitting 2 cell values by comma, then sumproduct Thanks:  1 Post #5344134 (1) Likes: 0

1. ## 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)

2. ## 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

 A B C 1 5,10 200,100 2000 2 1,2,3 50,100,150 700

 Cell Formula 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

3. ## Re: splitting 2 cell values by comma, then sumproduct

I really appreciate for your hard work. thank you.

4. ## Re: splitting 2 cell values by comma, then sumproduct

Originally Posted by antenkt
I really appreciate for your hard work. thank you.
You're welcome.