Use of sumproduct() in EXCEL

[Guest]

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,D1100=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.

 

[Bob Phillips]

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

Sorry didnt work. Extract #Δ/Υ

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

More detail?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

Yes pls. I try it, but didnt work.

More detail?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Harlan Grove]

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,D1100=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.

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
A1100 contain numbers, the formulas

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

and

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

return the same result.

 

[Guest]

I found what happened. I used different number of cells. ( A1:A100*B1:B100 +
C1:C67*D167+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.