Sum of products

[Rich Pasco]

Given two long columns of numbers, is there a simple expression which
yields the sum of the products of the numbers in one column times
their partners in the other column? I want to use a range operator
and not have to write a zillion plus signs.

In other words, I want to emulate an expression like

A1*B1 + A2*B2 + A3*B3 + A4*B4 + ... + A100*B100

with an expression that just references A1, B1, A100, and B100.

An obvious work-around is to define a third column to hold the products
of the first two. That is, let C1=A1*B1, C2=A2*B2 ... C100=A100*B100.
Then the expression I want is SUM(C1:C100).

Can I write a formula to find the sum without first defining column C?

- Rich

 

[MartinW]

Hi Rich,

You had it all correct, just had to put sum and
product into one word!

=SUMPRODUCT(A1:A100,B1:B100)

Check out this link for a whole lot more on SUMPRODUCT

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Martin

 

[Rich Pasco]

PERFECT! Exactly what I was looking for. Thanks!

- Rich