Multiple Sheet Sum / Product

[Gary Thomson]

I asked the following question earlier today:

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

and got the following answer:

=PRODUCT(Sheet1:Sheet30!B2:C2),

Which I was afterwards assured didn't work. Is there a
way to do this??

 

[Peo Sjoblom]

No, you would need something like Laurent Longre's Morefunc to do that, it
can be downloaded here


http://longre.free.fr/english/

a pretty good description in English

http://www.rhdatasolutions.com/morefunc/

if you install it you can use something like


=SUMPRODUCT(THREED(Sheet1:Sheet30!B2),THREED(Sheet1:Sheet30!C2))

 

[Harlan Grove]

I asked the following question earlier today:

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

and got the following answer:

=PRODUCT(Sheet1:Sheet30!B2:C2),

Which I was afterwards assured didn't work. Is there a
way to do this??

PRODUCT doesn't work because it multiplies all its terms rather than
multiplying pairs then summing those results. But it could be done using

=SUMPRODUCT(N(INDIRECT("Sheet"&{1,2,...,30}&"!B2")),
N(INDIRECT("Sheet"&{1,2,...,30}&"!C2")))

 

[Gary Thomson]

Brilliant, that works, but suppose that I want it to do
the following:

Sheet1:

B2: a
C2: £10


Sheet2:

B2: c
C2: £12


Sheet 3:

B2:
C2: £19

and so on up to sheet30.

Now for each sheet from sheet 1 to sheet 30, if B2
contains any value (i.e. it could be
a,b,c,...,x,y,z,0,1,2,...,8,9, etc) then this represents a
1, if not, it represents a zero.

To do this I used the COUNTA function, so that I had (in,
say, Sheet31)

=COUNTA(Sheet1!B2)*Sheet1!C2+COUNTA(Sheet2!B2)*Sheet2!
C2+....+COUNTA(Sheet30!B2)*Sheet30!C2

How could I implement the formula you gave below to do
this???

 

[Peo Sjoblom]

One way

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:30"))&"!B2"),"<>"&""))

--

Regards,

Peo Sjoblom


Brilliant, that works, but suppose that I want it to do
the following:

Sheet1:

B2: a
C2: £10


Sheet2:

B2: c
C2: £12


Sheet 3:

B2:
C2: £19

and so on up to sheet30.

Now for each sheet from sheet 1 to sheet 30, if B2
contains any value (i.e. it could be
a,b,c,...,x,y,z,0,1,2,...,8,9, etc) then this represents a
1, if not, it represents a zero.

To do this I used the COUNTA function, so that I had (in,
say, Sheet31)

=COUNTA(Sheet1!B2)*Sheet1!C2+COUNTA(Sheet2!B2)*Sheet2!
C2+....+COUNTA(Sheet30!B2)*Sheet30!C2

How could I implement the formula you gave below to do
this???