I do have about 10 sheets with data.
I want to have the sum of products of two identical cells in each sheet.

something like:
sumproduct(sheet1:sheet10!a1,sheet1:sheet10!b1)
which should return me the sum of 10 products.

However I do get a #ref error.

Any other solution?

Maybe,

=SUM(Sheet1:Sheet3!A1)+SUM(Sheet1:Sheet3!B1)

Obviouslt change 3 to 10.

Mike

"rob" wrote:

I do have about 10 sheets with data.
I want to have the sum of products of two identical cells in each sheet.

something like:
sumproduct(sheet1:sheet10!a1,sheet1:sheet10!b1)
which should return me the sum of 10 products.

However I do get a #ref error.

Any other solution?

Not clear what you want ... does A contain product ID and B a value?

=SUM(Sheet1:Sheet10!A1:B1) will sum A & B

=SUM(Sheet1:Sheet10!A1) will sum A


"rob" wrote:

I do have about 10 sheets with data.
I want to have the sum of products of two identical cells in each sheet.

something like:
sumproduct(sheet1:sheet10!a1,sheet1:sheet10!b1)
which should return me the sum of 10 products.

However I do get a #ref error.

Any other solution?

What I want is the following:

Sheet 1 contains 5 in A1 and 7 in B1
Sheet 2 contains 3 in A1 and 7 in B1
Sheet 3 contains 6 in A1 and 2 in B1

I would like to see a formula that returns me the sum of the product A1*B1
So in fact: 5 * 7 + 3 * 7 + 6 * 2 = 68


"Toppers" wrote:

Not clear what you want ... does A contain product ID and B a value?

=SUM(Sheet1:Sheet10!A1:B1) will sum A & B

=SUM(Sheet1:Sheet10!A1) will sum A


"rob" wrote:

I do have about 10 sheets with data.
I want to have the sum of products of two identical cells in each sheet.

something like:
sumproduct(sheet1:sheet10!a1,sheet1:sheet10!b1)
which should return me the sum of 10 products.

However I do get a #ref error.

Any other solution?

Not very elegant, but this works
=(Sheet1!A1*Sheet1!B1)+(Sheet2!A1*Sheet2!B1)+(Shee t3!A1*Sheet3!B1)
Use copy&paste within Formula Bar to extend to 10 sheets
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rob" wrote in message
...
What I want is the following:

Sheet 1 contains 5 in A1 and 7 in B1
Sheet 2 contains 3 in A1 and 7 in B1
Sheet 3 contains 6 in A1 and 2 in B1

I would like to see a formula that returns me the sum of the product A1*B1
So in fact: 5 * 7 + 3 * 7 + 6 * 2 = 68


"Toppers" wrote:

Not clear what you want ... does A contain product ID and B a value?

=SUM(Sheet1:Sheet10!A1:B1) will sum A & B

=SUM(Sheet1:Sheet10!A1) will sum A


"rob" wrote:

I do have about 10 sheets with data.
I want to have the sum of products of two identical cells in each
sheet.

something like:
sumproduct(sheet1:sheet10!a1,sheet1:sheet10!b1)
which should return me the sum of 10 products.

However I do get a #ref error.

Any other solution?