Excel: Scalar product of two ranges

Question

I am trying to find a proper way to calculate the scalar product of two ranges. For instance, the product of A1:A3 and B1:B3 would be A1*B1 + A2*B2 + A3*B3. Is there a good way to do this? Hardcoding this calculation is quite a tedious thing to do with large ranges.

Threepence

Answer 1

Use this one

=SUMPRODUCT(A1:A3,B1:B3)

Iroquoian

Note that if one array is in a row and the other is in a column you need to use =SUMPRODUCT(A1:A3, TRANSPOSE(B1:B3)) and then press Crtl+Shift+Enter to confirm. – Effloresce

Answer 2

SUMPRODUCT formula – syntax and usage

Excel SUMPRODUCT formula - tutorialThe sum-product formula syntax is very simple. It takes 1 or more arrays of numbers and gets the sum of products of corresponding numbers.

The syntax is =SUMPRODUCT (list 1, list 2 ...)

Noland

Answer 3

Update on 11/30/2022

Considering the Excel Spill feature, now you can use SUM for most of the cases SUMPRODUCT was used, for example:

=SUM(A1:A3*B1:B3)

Here is the output:

Bernardobernarr