SUM of a 3D calculation

[Werner Rohrmoser]

Hi,

I have a book with Sheet1 through Sheet10.
On Sheet2 through Sheet10 there are numbers in cell "A1", "A2" and
"A3".
What I'd like to do is to calculate directly on Sheet1 the SUM of the
single calculations on Sheet2 through Sheet10, which is "A1/A2*A3".
It would be the result of
SUM(Sheet2!A1/Sheet2!A2*Sheet2!A3, ...., Sheet10!A1/Sheet10!A2*Sheet10!
A3).
Yes, Ican calculate the formula on each sheet and use the SUM formula
with a 3D reference,
but this is not the solution I'm looking for.
Is there an array formula, which can do that?
Thanks.

Excel XP SP3
WIN XP SP3

Regards
Werner

 

[Mike H]

Hi,

I'm not sure you can easilly do the calculation on sheet 1 but you can do this

=SUM(Sheet2:Sheet10!A4)

Where A4 on each sheet contains the formula =A1/A2*A3

You don't need to use A4 it can be a cell out of the way somewhere.

Mike

 

[Bam]

=SUM(Sheet2:Sheet10!A1:A3)

If the sheets are named differently, use the shift key {after =sum( } to
group the sheets (Click Sheet2 press Shift - Click sheet10) and highlight
cells A1:A3.

They must be consecutive though. Eg: if you insert a sheet between Sheet2 &
Sheet10 - and populate A1:A3 with a number, it will be included in the
formula.

Bam

 

[Lori]

Maybe try this:

=SUMPRODUCT(N(INDIRECT("sheet"&ROW(2:9)&"!a1"))/SUBTOTAL(6,INDIRECT("sheet"&ROW(2:9)&"!a2:a3")))

 

[Werner Rohrmoser]

Hi Lori,

I modified your approach a little bit, so that I've got now what I
need.
"Blaetter" is a named range, where I have stored all sheets which have
to be processed.
This is the sum of a multiplication of all D24 cells with all B81
cells devided by all B96 cells,
let's say an operation with three arrays across the sheets contained
in "Blaetter" and then totaled.

=SUM(N(INDIRECT("'"&Blaetter&"'!D24"))*N(INDIRECT("'"&Blaetter&"'!
B81"))/N(INDIRECT("'"&Blaetter&"'!B96"))).

Thank you to get me started.

Werner

 

[Bam]

You could also try Data / Consolidate.

Do a search in help for Consolidate and it shows you what's available.

Excel2003.

Bam.

 

[Werner Rohrmoser]

Bam,

thanks for your contribution.

Regards
Werner