sumproduct function in excel #value

Question

I have the following excel sheet

and when I type=SUMPRODUCT(A1:D1,E1:E4)

I get #VALUE! Why is this? all are numeric fields

Answer 1

As has been noted by other responders, you get the error because the dimensions are not the same -- one being horizontal and the other being vertical.

To expand on my comment to your answer, if what you want to do is:

A1*E1+B1*E2+C1*E3+D1*E4

this is a matrix product and the result is given simply by

=MMULT(A1:D1,E1:E4)

Please search the internet for detailed descriptions of matrix multiplication, as it is fairly complicated.

On the other hand, if you want to multiply every entry in your horizontal array, by every entry in the vertical array:

A1*E1+A1*E2+A1*E3+A1*E4+B1*E1+B1*E2+ ...

then you could use the array-entered (confirm with ctrl+shift+enter) formula:

=SUM(A1:D1*E1:E4)

Answer 2

The A1:D1 range is 1 row × 4 columns. The E1:E4 range is 4 rows × 1 column. While they each contain 4 cells, they are not the same dimension; e.g. one is the transposition of the other. Roughly speaking, they are the same size, just not the same dimensions.

You can use the TRANSPOSE function to flip one of the ranges into the orientation of the other but you will have to use it as an array formula to provide the extra layer of sub-processing. Either of these will suffice as long as you use CSE to finalize the formula.

=SUMPRODUCT((A1:D1), TRANSPOSE(E1:E4))
=SUM((A1:D1)*TRANSPOSE(E1:E4))

Array formulas need to be finalized with Ctrl+Shift+Enter↵.

Answer 3

SUMPRODUCT's arrays should be of equal length.

You can do =SUMPRODUCT(A1:E4) or =SUMPRODUCT(A1:E1,A2:E2,A3:E3,A4:E4) as is suitable for your case.

From: https://support.office.com/en-nz/article/SUMPRODUCT-function-4e0bffa7-4291-4635-a61f-6aaa9399e7ff

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.