66

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.

Lee White
  • 3,649
  • 8
  • 37
  • 62

3 Answers3

89

Use this one

=SUMPRODUCT(A1:A3,B1:B3)
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • 4
    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. – Guy s Jan 21 '20 at 08:02
13

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 ...)

enter image description here

0

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: sample output

David Leal
  • 6,373
  • 4
  • 29
  • 56