Use of sumproduct() in EXCEL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Νικος - 25 Jan 2007 08:57 GMT

Hi.
Does anyone know if I can use the sumproduct() formula in parallel columns?
For example lets say that I have n columns A1:A100 =1:100,
B1:B100=10:1000,C1:C100=101:200,D1:D100=1010:2000,…,etc.
I would like to calculate the sum A*B + C*D + …..+ Xn*Xn+1, using
sumproduct() formula and without say sumproduct()1 + sumproduct()2+…..+
sumproduct()n.
The sumproduct formula must be used only once.
Any idea
Thank you.

Reply to this Message

Bob Phillips - 25 Jan 2007 09:20 GMT

=SUMPRODUCT((A1:A100*B1:B100)+(C1:C100*D1:D100))

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi.
> Does anyone know if I can use the sumproduct() formula in parallel
[quoted text clipped - 7 lines]
> Any idea
> Thank you.

Reply to this Message

Νικος - 25 Jan 2007 10:46 GMT

Sorry didnt work. Extract #Δ/Υ

> =SUMPRODUCT((A1:A100*B1:B100)+(C1:C100*D1:D100))
>
[quoted text clipped - 9 lines]
> > Any idea
> > Thank you.

Reply to this Message

Bob Phillips - 25 Jan 2007 12:26 GMT

More detail?

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Sorry didnt work. Extract #?/?
>
[quoted text clipped - 11 lines]
>> > Any idea
>> > Thank you.

Reply to this Message

Νικος - 25 Jan 2007 12:38 GMT

Yes pls. I try it, but didnt work.

> More detail?
>
[quoted text clipped - 13 lines]
> >> > Any idea
> >> > Thank you.

Reply to this Message

Bob Phillips - 25 Jan 2007 13:03 GMT

No, you give more detail in how it didn't work. I tested it and it worked
exactly as I read your post.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Yes pls. I try it, but didnt work.
>
[quoted text clipped - 16 lines]
>> >> > Any idea
>> >> > Thank you.

Reply to this Message

Harlan Grove - 25 Jan 2007 18:36 GMT

Νικος <@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 =1:100,
[quoted text clipped - 3 lines]
>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

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

and

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

return the same result.

Reply to this Message

Νικος - 26 Jan 2007 16:51 GMT

I found what happened. I used different number of cells. ( A1:A100*B1:B100 +
C1:C67*D1:D67+E1:E94*F1:F94 …etc. ) If i use same number in every column its
ok. I didn’t say that from the beginning by mistake.

> Νικος <@discussions.microsoft.com> wrote...
> >Does anyone know if I can use the sumproduct() formula in parallel columns?
[quoted text clipped - 19 lines]
>
> return the same result.

Reply to this Message

Rate this thread: