Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
count cells over multiple sheets | Excel Worksheet Functions |