- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel
- 2007 January
- Re: Use of sumproduct() in EXCEL

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.

- Re: Use of sumproduct() in EXCEL posted by ÎÎ¹ÎºÎ¿Ï‚ on Fri, 26 Jan 2007

Use of sumproduct() in EXCEL posted by ÎÎ¹ÎºÎ¿Ï‚ on Thu, 25 Jan 2007