How to sumproduct cell arrays?

[hpr0009]

Typing =SUMPRODUCT({0.5,1,0.5},{4,3,2}) into a cell gives a result of 6. I’m trying to get the same result by putting {0.5,1,0.5} in cell A1, {4,3,2} in cell A2, and =SUMPRODUCT(A1,A2) in cell A3, but when I try this I get the #VALUE error in A3. Is there a way to carry out this SUMPRODUCT with the arrays being drawn from their own separate cells?

 

[Aladin Akyurek]

No, not without an evaluation function.

 

[Rick Rothstein]

Typing =SUMPRODUCT({0.5,1,0.5},{4,3,2}) into a cell gives a result of 6. I’m trying to get the same result by putting {0.5,1,0.5} in cell A1, {4,3,2} in cell A2, and =SUMPRODUCT(A1,A2) in cell A3, but when I try this I get the #VALUE error in A3. Is there a way to carry out this SUMPRODUCT with the arrays being drawn from their own separate cells?

I know it is a lot of "ifs", but if A1 contains the formula ={0.5,1,0.5} and A2 contains the formula ={4,3,2} and if you know that independently that A1 and A2 contain three elements each, then you can get the result you are after with this formula...

=SUMPRODUCT(A1*{1,1,1},A2*{1,1,1})

 

[Aladin Akyurek]

I know it is a lot of "ifs", but if A1 contains the formula ={0.5,1,0.5} and A2 contains the formula ={4,3,2} and if you know that independently that A1 and A2 contain three elements each, then you can get the result you are after with this formula...

=SUMPRODUCT(A1*{1,1,1},A2*{1,1,1})

Rick,

I don't see the point as it's not much different from in-linening...

=SUMPRODUCT({0.5,1,0.5},{4,3,2})

Better to have:

=SUMPRODUCT(EVAL(A1),EVAL(A2))

but, alas, Excel misses such a native function. (Of course we have Longre's EVAL and Williams's.)

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a way to carry out this SUMPRODUCT with the arrays being drawn from their own separate cells?

If there will always be 3 values in the cell array, try the B1 formula below.
If the number of terms is unknown, try the B4 formula. In this case, if there could be a lot of numbers in each array, you may need to increase the '20' and '19' values in the formula.

Book1
	A	B
1	{0.5,1,0.5}	6
2	{4,3,2}
3
4	{0.5,1,0.5,3,0.2}	10
5	{4,3,2,1,5}
Sumproduct

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"{",""),"}",""),",",REPT(" ",20)),{1,21,41},20)*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"{",""),"}",""),",",REPT(" ",20)),{1,21,41},20))
B4	=SUMPRODUCT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"{",""),"}",""),",",REPT(" ",20)),ROW(INDIRECT("1:"&LEN(A4)-LEN(SUBSTITUTE(A4,",",""))+1))*20-19,20)*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,"{",""),"}",""),",",REPT(" ",20)),ROW(INDIRECT("1:"&LEN(A5)-LEN(SUBSTITUTE(A5,",",""))+1))*20-19,20))

 

[Rick Rothstein]

Rick,

I don't see the point as it's not much different from in-linening...

=SUMPRODUCT({0.5,1,0.5},{4,3,2})

It allows the two arrays to be in different cells which appeared to me to be what the OP was aiming for.

 

[Peter_SSs]

.. or a little bit shorter this way

Book1
	A	B
1	{0.5,1,0.5}	6
2	{4,3,2}
3
4	{0.5,1,0.5,3,0.2}	10
5	{4,3,2,1,5}
Sumproduct (2)

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(MID(SUBSTITUTE(MID(A1,2,LEN(A1)-2),",",REPT(" ",20)),{1,21,41},20)*MID(SUBSTITUTE(MID(A2,2,LEN(A2)-2),",",REPT(" ",20)),{1,21,41},20))
B4	=SUMPRODUCT(MID(SUBSTITUTE(MID(A4,2,LEN(A4)-2),",",REPT(" ",20)),ROW(INDIRECT("1:"&LEN(A4)-LEN(SUBSTITUTE(A4,",",""))+1))*20-19,20) *MID(SUBSTITUTE(MID(A5,2,LEN(A5)-2),",",REPT(" ",20)),ROW(INDIRECT("1:"&LEN(A5)-LEN(SUBSTITUTE(A5,",",""))+1))*20-19,20))

 

[Tetra201]

Late to the party, but maybe:

Cell A1: {0.5,1,0.5}
Cell A2: {4,3,2}
Name: Array1 Refers to: =EVALUATE(Sheet1!$A$1)
Name: Array2 Refers to: =EVALUATE(Sheet1!$A$2)
Formula: =SUMPRODUCT(Array1,Array2)

 

[63falcondude]

Cross-posted here:

https://www.excelforum.com/excel-formulas-and-functions/1206520-how-to-sumproduct-cell-arrays.html

 

[hpr0009]

Thanks for the help everyone, I learned a few things! Seems like the best thing for me to do at this point is just keep it simple and put each number in it's own cell.