Multiplying numbers separated by commas in multiple cells

[mikeloz11]

the formula below gives me the total in D1
in E1 i need to get the meters squared by multiplying each number individually by the qty in A1
is this possible?

=SUM(VALUE(CELLSPLIT(B1,",")))+SUM(VALUE(CELLSPLIT(C1,",")))

 

[Cubist]

in E1 i need to get the meters squared by multiplying each number individually by the qty in A1

This doesn't make sense to me. Can you show the manual calculation?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2	1	.273, .550, 1.25	3.048, 2.356, 5.886		13.363
Data

Cell Formulas
Range	Formula
E2	E2	=SUM(TEXTSPLIT(TEXTJOIN(",",,B2,C2),",")*A2)

 

[Scott Huish]

Same, but with 1 less function
=SUM(--TEXTSPLIT(B2&","&C2,","))*A2

 

[mikeloz11]

This doesn't make sense to me. Can you show the manual calculation?

B and C should total 9.49 m2
.273x3.048 = .83 m2
.550x2.356 = 1.30 m2
1.250x5.886 = 7.36 m2
for a grand total of 9.49 m2

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1
2	1	.273, .550, 1.25	3.048, 2.356, 5.886		9.485404
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--TEXTSPLIT(B2,","),--TEXTSPLIT(C2,","))

 

[mikeloz11]

Try:

Book1
	A	B	C	D	E
1
2	1	.273, .550, 1.25	3.048, 2.356, 5.886		9.485404
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--TEXTSPLIT(B2,","),--TEXTSPLIT(C2,","))

Thank you so very very much! that worked perfectly!
greatly appciated!

 

[Scott Huish]

Thank you so very very much! that worked perfectly!
greatly appciated!

That will only work if the number in A2 is always 1, it never multiplies that value.

 

[mikeloz11]

That will only work if the number in A2 is always 1, it never multiplies that value.

Thank you.

 

[Rick Rothstein]

Try:

Book1
	A	B	C	D	E
1
2	1	.273, .550, 1.25	3.048, 2.356, 5.886		9.485404
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--TEXTSPLIT(B2,","),--TEXTSPLIT(C2,","))

Why not just this...

=SUM(TEXTSPLIT(B2,",")*TEXTSPLIT(C2,","))