Array Fromula

[Pmxgs]

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets (all
of these sheets are equally arranged), so i want to sum all the figures from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows how i
can do this, please help.

thanks

 

[Guest]

Perhaps a 3D reference?

=SUM(Sheet1:Sheet100!A1)

will sum cell A1 on Sheet1 through Sheet100 (change the beginning and ending
sheet names to whatever yours are called).

 

[Domenic]

Try...

=SUMPRODUCT(N(INDIRECT("'"&E8:E108&"'!"&ADDRESS(C8,D8))))

Hope this helps!

 

[Roger Govier]

Hi

Rather than trying to use an array formula, just create a new sheet
called First and another called Last.
Drag these sheets to positions where your 100 sheets to be added are
"sandwiched" between them with your summary sheet outside of this
sandwich.
On your Summary sheet
=SUM(First:Last!C88)
You can hide First and Last if you wish

 

[Pmxgs]

Perhaps I wasn't very clear.
I can't use a 3d reference because i don't want to sum all of the sheets.
That's why i need a range where i can type the name of the sheets i want to
sum. In my example would be E8:E108.

 

[Pmxgs]

Thanks a lot Domenic. It works!
The problem was related to the way i wrote the indirect function and address
function. The way you wrote works fine.
But the sum function works ok, sumproduct it's not needed.
Thanks again.

 

[Domenic]

Thanks a lot Domenic. It works!

You're very welcome! Glad I could help!

The way you wrote works fine.
But the sum function works ok, sumproduct it's not needed.

Of course, using SUM instead of SUMPRODUCT will require the formula to
be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.