COUNT over several sheets
 

Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals for
each month and present it as an annual figure on the final sheet (sheet #13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

Hi

if you want to count entries in a cell then use a formula similar to
=COUNT(Jan:Dec!A1)
where Jan is the name of the first sheet and Dec is the name of the last and
the sheets you want to include are between these two

if you want to total the cells try
=SUM(Jan:Dec!A1)

you might also like to check out data / consolidation for this as well.

Cheers
JulieD

"Fybo" wrote in message
...
Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals
for
each month and present it as an annual figure on the final sheet (sheet
#13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

=SUM('First:Last'!Range_to_sum)


--

Regards,

Peo Sjoblom


"Fybo" wrote in message
...
Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals
for
each month and present it as an annual figure on the final sheet (sheet
#13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

Assuming that each sheet contains the 'Total' figure in cell A100, try...

=SUM(Sheet1:Sheet12!A100)

Hope this helps!

In article ,
"Fybo" wrote:

Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals for
each month and present it as an annual figure on the final sheet (sheet #13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

Thanks for all your help guys...and quick!!

I've tried =SUM(January 2005:December 2005!D34) but the cell just displays
the function and not the result. Is the function wrong or am I missing
something.

Have I got all the spaces and characters correct?

Thanks again, Fybo.

"Domenic" wrote:

Assuming that each sheet contains the 'Total' figure in cell A100, try...

=SUM(Sheet1:Sheet12!A100)

Hope this helps!

In article ,
"Fybo" wrote:

Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals for
each month and present it as an annual figure on the final sheet (sheet #13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

Hi

if your sheet names have spaces in them you'll need modify the formula to
=SUM('January 2005:December 2005'!D34)

that's a single quote mark before the January and before the !

Cheers
JulieD

"Fybo" wrote in message
...
Thanks for all your help guys...and quick!!

I've tried =SUM(January 2005:December 2005!D34) but the cell just displays
the function and not the result. Is the function wrong or am I missing
something.

Have I got all the spaces and characters correct?

Thanks again, Fybo.

"Domenic" wrote:

Assuming that each sheet contains the 'Total' figure in cell A100, try...

=SUM(Sheet1:Sheet12!A100)

Hope this helps!

In article ,
"Fybo" wrote:

Hi,

I am compiling an annual report with 12 sheets (one per month) and a
final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the
totals for
each month and present it as an annual figure on the final sheet (sheet
#13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.

You may want to consider Peo's solution.

If you insert a couple of blank worksheets (named First and Last) to the left
and right of the worksheets to be summed, you use Peo's formula:

=SUM('First:Last'!Range_to_sum)

Then if you ever want to exclude a month/worksheet, you can just drag it outside
those two "bookend" worksheets.





Fybo wrote:

Thanks for all your help guys...and quick!!

I've tried =SUM(January 2005:December 2005!D34) but the cell just displays
the function and not the result. Is the function wrong or am I missing
something.

Have I got all the spaces and characters correct?

Thanks again, Fybo.

"Domenic" wrote:

Assuming that each sheet contains the 'Total' figure in cell A100, try...

=SUM(Sheet1:Sheet12!A100)

Hope this helps!

In article ,
"Fybo" wrote:

Hi,

I am compiling an annual report with 12 sheets (one per month) and a final
13th summary sheet.

Each of the 12 monthly sheets are identical and I want to add the totals for
each month and present it as an annual figure on the final sheet (sheet #13).

I cannot figure out how to get Excel to look at all my sheets and total
these on the final.

Can it be done? Can anyone help!!

Many thanks,

Fybo.


--

Dave Peterson