SUMPRODUCT does not work

[Guest]

Hi,

It doesn't look like I can get SUMPRODUCT to work on a vertical array and a
horizental array. For example, SUMPRODUCT(A2:A5, B6:E6) gives #VALUE!.
SUMPRODUCT(A2:A5, TRANSPOSE(B6:E6)) or SUMPRODUCT(TRANSPOSE(A2:A5), B6:E6))
also does not work.

I guess I can use MMULT(B6:E6, A2:A5), but does any one know how to get
SUMPRODUCT to work?

Thanks,
Geoffrey

 

[hgrove]

Geoffrey Zhu wrote...

It doesn't look like I can get SUMPRODUCT to work on a vertical
array and a horizental array. For example,

SUMPRODUCT(A2:A5,B6:E6)

gives #VALUE!.

SUMPRODUCT(A2:A5, TRANSPOSE(B6:E6))
or
SUMPRODUCT(TRANSPOSE(A2:A5), B6:E6))

also does not work.

Unfortunately, arguments to SUMPRODUCT must be the same size *and
shape, and TRANSPOSE is one of the older functions that requires arra
entry in order to return an array when passed anything other than
scalar or a constant array.

FWIW, you could use the *array* formula

=SUM(A2:A5, TRANSPOSE(B6:E6))

I guess I can use MMULT(B6:E6, A2:A5), but does any one know
how to get SUMPRODUCT to work?

In this case, there's no benefit to SUMPRODUCT. MMULT is the bes
function to use for this

 

[Guest]

Hi Hgrove,

TRANSPOSE is one of the older functions that requires array
entry in order to return an array when passed anything other than a
scalar or a constant array.

Thanks for your reply. It makes the problem a lot cleaner. I don't
understand what you mean by saying 'TRANSPOSE requires array entry in order
to return an array.' Does 'array entry' mean I have to enter it as an 'array
formula?' If that is so, I guess that answers my other question about
TRANSPOSE and MMULT, too.

 

[Myrna Larson]

That was my experience also, hence I gave you an array formula,

=SUM((A2:A5)*TRANSPOSE(B6:E6))

Maybe you missed my post.