Summing over multiple sheets

[Gary Thomson]

2 Questions:

(1) Is there a quicker way of summing up the same cell
over 30 spreadsheets?

i.e.
=Sheet1!B2+Sheet2!B2+Sheet3!B2+Sheet4!B2+Sheet5!
B2+...+Sheet29!B2+Sheet30!B2



(2) Is there a quicker way again of summing the
multiplication of 2 values in each sheet over a number of
sheets?

i.e.
=Sheet1!B2*Sheet1!C2+Sheet2!B2*Sheet2!C2+Sheet3!B2*Sheet3!
C2+Sheet4!B2*Sheet4!C2+Sheet5!B2*Sheet5!C2+...+Sheet29!
B2*Sheet29!C2+Sheet30!B2*Sheet30!C2

 

[Peo Sjoblom]

One way

=SUM(Sheet1:Sheet30!B2)

 

[Katherine Coombs]

Hi Gary,

What you are after is a 3D sum and 3D product.

(1) try =SUM(Sheet1:Sheet30!B2)
(2) try =PRODUCT(Sheet1:Sheet30!B2:C2)

HTH,
Katherine

 

[Katherine Coombs]

Sorry Gary - I didn't read your (2) properly and the formula that I gave you
will perform a running multiplication of all cells B2 and C2, ie it would do
B2*C2*B2*C2 etc on all sheets, rather than just adding up the various sets
of multiplications so please don't use that.

Looking at your request I'm not sure of the best way to proceed with that
one - I will be interested to hear others' responses.

Cheers,
Katherine