Summing Multiple sheets (indirect formula)

Hi,
I have a summary sheet with the following formula

Please Login or Register  to view this content.

The ‘1:3’ is the sheet range I want it to sum. This however changes as there could be at times over 100 sheets in which I need it to sum.

Can anyone suggest a formula which I could use where somewhere in the summary sheet I input the sheets I want it to sum e.g. “1:3” and the formula would then sum all sheets in this range.

The problem I have with the current formula is when I delete all sheets e.g. sheets “1”, “2” and “3” and then recreate them all the formulas on the summary sheet return #REF.

I have attached an example...

Add two new sheets 'First' and 'Last' to your workbook. Position the sheets you want to sum together between this new sheets.
With the formula =SUM(First:Last!F8) you can sum cell F8.

See the attached file.Sum multiple sheets.xls

Hi Paul

This works for me.

=SUMPRODUCT(--N(INDIRECT(""&ROW(1:3)&"!F9")))

Hope, for you, too.

This should work,

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:74"))&"'!"&ADDRESS(ROWS($1:1)+55,COLUMNS($A:A)+5))))

Then copy across & down.

CELL function is useful for this - try this version copied across

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:74"))&"'!"&CELL("address",F56))))

Nice one DLL with CELL

@DDL

Excellent formula. CELL function, makes the difference.