Offset formula across multiple sheets

[flexinau]

Using the formula below produces an error. Is there a way to sum across sheets to get the results I am trying to produce with the formula below?

=SUM(OFFSET(Sheet1!:Sheet5!A9,1,5))

thanks

 

[Domenic]

Try...

=SUMPRODUCT(N(OFFSET(INDIRECT("Sheet"&ROW(INDIRECT("1:5"))&"!A9"),1,5)))

or

=SUMPRODUCT(N(OFFSET(INDIRECT("'"&A1:A5&"'!A9"),1,5)))

...where A1:A5 contains your sheet names.

Hope this helps!

 

[flexinau]

I used the second formula and it work but I have a question.

If the offset formula returns a value, then why use the n formula to convert to a number?

 

[Domenic]

If the offset formula returns a value, then why use the n formula to convert to a number?

Actually, OFFSET/INDIRECT returns an 'array of references'. The N() function is used to de-reference. Basically, to make the values available from the references.

 

[flexinau]

thanks Domenic

 

[Aladin Akyurek]

What is the reason for specifying the target cell to sum by means of volatile functions (OFFSET, INDIRECT) which also forces you to abandon a simple 3D SUM?