SUMPRODUCT does not work

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
Click to expand...

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?
Click to expand...

In this case, there's no benefit to SUMPRODUCT. MMULT is the bes
function to use for this
 
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.
Click to expand...

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.
 
That was my experience also, hence I gave you an array formula,

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

Maybe you missed my post.
 
Back
Top