Re: Use of sumproduct() in EXCEL

Giganews Newsgroups
Subject: Re: Use of sumproduct() in EXCEL
Posted by:  Harlan Grove (hrlng…@aol.com)
Date: 25 Jan 2007

=CE=9D=CE=B9=CE=BA=CE=BF=CF=82 <@discussions.microsoft.com> wrote...
>Does anyone know if I can use the sumproduct() formula in parallel columns?
>For example lets say that I have n columns A1:A100 =3D1:100,
>B1:B100=3D10:1000,C1:C100=3D101:200,D1:D100=3D1010:2000,=E2=80=A6,etc.
>I would like to calculate the sum A*B + C*D + =E2=80=A6..+ Xn*Xn+1, using
>sumproduct() formula and without say sumproduct()1 + sumproduct()2+=E2=80=
=A6..+
>sumproduct()n.
>The sumproduct formula must be used only once.

Why only one SUMPRODUCT call? Did you make a bet that this could be
done?

If you don't use multiple SUMPRODUCT calls, follow Bob Phillips
approach, which DOES work as written. That is, as long as all cells in
A1:D100 contain numbers, the formulas

=3DSUMPRODUCT(A1:A100,B1:B100)+SUMPRODUCT(C1:C100,D1:D100)

and

=3DSUMPRODUCT(A1:A100*B1:B100+C1:C100,D1:D100)

return the same result.

Replies

In response to

Use of sumproduct() in EXCEL posted by Νικος on Thu, 25 Jan 2007