Why does my simple Excel formula =SUMPRODUCT(A1:D1, A2:A5) return #VALUE error?

Question

I've got a horizontal range of numbers in cells, and a vertical range of numbers in some other cells. I want a product of those ranges. For example cells A1:D1 contain 1,2,3,4 and cells A2:A5 contain values 5, 6,7,8. I except to get an answer of 70.

I run =SUMPRODUCT(A1:D1, A2:A5) and it gives me #VALUE. Same problem for other simple examples like this. I have no idea why. According to all my sources I've read (including ChatGPT) this formula should work?

Tried various cell range lengths and values, in different positions, but same problem.

Answer 1

The formula in the OP is not working because as per MSFT Documentations, SUMPRODUCT() function returns the product of corresponding ranges or arrays, where the default operation is multiplication. However, one can use addition, subtraction or division as well, which is possible. Read here.

---

Now, the question is even though the data has same dimensions and same sizes its still not working because its not corresponding to each other in order to return the desired output 70. Two options, transpose one of the ranges so it becomes corresponding to one another and then apply the function.

---

• Using SUMPRODUCT() with TRANSPOSE() function:

---

=SUMPRODUCT(TRANSPOSE(A1:D1),A2:A5)

---

Or, Using SUM() with TOCOL()

=SUM(A2:A5*TOCOL(A1:D1))

---

Answer 2

You can use the MMULT function...

=MMULT(A1:D1,A2:A5)

Note, though, the formula will return #VALUE! if one or more cells are empty/blank.