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

[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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

[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

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

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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

"tacarme" <[email protected]> wrote in
message news:[email protected]...
>
> Will this formula be valid if we rename a sheet, say sheet 20 ?
> Thanks .
>
>
> --
> tacarme
> ------------------------------------------------------------------------
> tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
> View this thread: http://www.excelforum.com/showthread...hreadid=381871
>

[[email protected]]

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

http://www.bygsoftware.com/Excel/Int.../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