Sum a cell within all worksheets

[Irada Shamilova]

Hi!

I have a very big workbook containing 52 identical worksheets.
Plus Summary sheet in the same workbook identical to all worksheets.
I need a formula that will summarize every single cell all over the
worksheets. Something like consolidate but within the sheets in one
workbook.

For example:
=Sheet1!A1+Sheet2!A1+Sheet3!A1

I can't use this simple formula as:
1. Too many sheets
2. Sheet names are long
3. Formula becomes very big

Is there any other function that will make it?

Thanks in advance for your help.
Irada

 

[mangesh_yadav]

=SUM(Sheet1:Sheet3!A2)

or

=SUM(Sheet1:Sheet52!A2) in your case


Mangesh

 

[Irada Shamilova]

Exactly what I need!
Thanks!

 

[tacarme]

Will this formula be valid if we rename a sheet, say sheet 20 ?
Thanks .

 

[mangesh_yadav]

Yes, it should. And also if you add new sheets between the two sheets
specified in the formula.

Mangesh

 

[Robert_Steel]

there is an excellent method shown for summarizing sheets shown on the
bygsoftware site.

http://www.bygsoftware.com/Excel/InterFace/breadroll.htm

oddly called 'bread roll method'
but defiantly worth a look.

The gist is if you have the following sheet names

Summary, Bread, Area1, Area2, Area3, Roll, Area4
and you leave sheets Bread and Roll blank

If you put the formula =Sum(Bread:Roll!D3) it will return the sum of D3 in
all sheets between Bread and Roll ie. Area1-3.

You are then able to move sheets in and out from between Bread and Roll to
include or remove them from the summary.

I promise the real example is more clear.

HTH
RES

 

[Jim May]

But not so, if renamed sheet 20 is moved to the end (right-most-position),
meaning outside the internal index numbering scheme initially established.
HTH

 

[mangesh_yadav]

Hi Jim,

When I tried renaming the sheet, it didn't show the behaviour you
mentioned in your post. The formula accomodated it without any
problem.

Mangesh