| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function to sum same cell in several worksheets
Is there a function that will add the values of the same cell in several worksheets? Suppose I have 52 worskheets (one for each week in the year) and I want to add the values in cell D3 on each and place the result in another worksheet, is there a function that will save me having to type ='Sheet1'!D3+'Sheet2'!D3+'Sheet3'!D3+'Sheet4'!D3+' Sheet5'!D3+'Sheet6'!D3+'Sheet7'!D3 and so on? I tried =SUM('Sheet1'!D3:'Sheet52'!D3) but it looks as though the result is the sum of a block of cells spanning the 52 sheets. What I am after is something like =SUM('Sheet1'!:'Sheet52'!,D3). -- Stephen White ) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Function to sum same cell in several worksheets
I just asked a similar question that might be helpful:
http://www.microsoft.com/office/comm...&cr=&sloc=&p=1 If you take that solution and reserve one cell for each sheet, you can easily incorporate the values into a list on one 'summary' sheet. from there you can SUM them as normal. There may be a neater solution, but I believe this'll work. "Stephen White" wrote: Is there a function that will add the values of the same cell in several worksheets? Suppose I have 52 worskheets (one for each week in the year) and I want to add the values in cell D3 on each and place the result in another worksheet, is there a function that will save me having to type ='Sheet1'!D3+'Sheet2'!D3+'Sheet3'!D3+'Sheet4'!D3+' Sheet5'!D3+'Sheet6'!D3+'Sheet7'!D3 and so on? I tried =SUM('Sheet1'!D3:'Sheet52'!D3) but it looks as though the result is the sum of a block of cells spanning the 52 sheets. What I am after is something like =SUM('Sheet1'!:'Sheet52'!,D3). -- Stephen White ) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Function to sum same cell in several worksheets
=SUM(Sheet1:Sheet52!,D3)
or =SUM('Sheet1:Sheet52'!,D3) -- David Biddulph "Stephen White" wrote in message ... Is there a function that will add the values of the same cell in several worksheets? Suppose I have 52 worskheets (one for each week in the year) and I want to add the values in cell D3 on each and place the result in another worksheet, is there a function that will save me having to type ='Sheet1'!D3+'Sheet2'!D3+'Sheet3'!D3+'Sheet4'!D3+' Sheet5'!D3+'Sheet6'!D3+'Sheet7'!D3 and so on? I tried =SUM('Sheet1'!D3:'Sheet52'!D3) but it looks as though the result is the sum of a block of cells spanning the 52 sheets. What I am after is something like =SUM('Sheet1'!:'Sheet52'!,D3). -- Stephen White ) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Function to sum same cell in several worksheets
I think that David meant to also remove the extraneous comma you had placed in your formula =SUM(sheet1:Sheet3!D3) or =SUM('Sheet1:Sheet52'!D3) -- Regards Roger Govier "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUM(Sheet1:Sheet52!,D3) or =SUM('Sheet1:Sheet52'!,D3) -- David Biddulph "Stephen White" wrote in message ... Is there a function that will add the values of the same cell in several worksheets? Suppose I have 52 worskheets (one for each week in the year) and I want to add the values in cell D3 on each and place the result in another worksheet, is there a function that will save me having to type ='Sheet1'!D3+'Sheet2'!D3+'Sheet3'!D3+'Sheet4'!D3+' Sheet5'!D3+'Sheet6'!D3+'Sheet7'!D3 and so on? I tried =SUM('Sheet1'!D3:'Sheet52'!D3) but it looks as though the result is the sum of a block of cells spanning the 52 sheets. What I am after is something like =SUM('Sheet1'!:'Sheet52'!,D3). -- Stephen White ) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Function to sum same cell in several worksheets
Abolutely! Well spotted, Roger.
-- David Biddulph "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... I think that David meant to also remove the extraneous comma you had placed in your formula =SUM(sheet1:Sheet3!D3) or =SUM('Sheet1:Sheet52'!D3) -- Regards Roger Govier "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUM(Sheet1:Sheet52!,D3) or =SUM('Sheet1:Sheet52'!,D3) -- David Biddulph "Stephen White" wrote in message ... Is there a function that will add the values of the same cell in several worksheets? Suppose I have 52 worskheets (one for each week in the year) and I want to add the values in cell D3 on each and place the result in another worksheet, is there a function that will save me having to type ='Sheet1'!D3+'Sheet2'!D3+'Sheet3'!D3+'Sheet4'!D3+' Sheet5'!D3+'Sheet6'!D3+'Sheet7'!D3 and so on? I tried =SUM('Sheet1'!D3:'Sheet52'!D3) but it looks as though the result is the sum of a block of cells spanning the 52 sheets. What I am after is something like =SUM('Sheet1'!:'Sheet52'!,D3). -- Stephen White ) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Function to sum same cell in several worksheets
Many thanks to mattjdohm, David and Roger. I had begun to think that a summary sheet was part of the answer but then David and Roger told me the syntax for the SUM function. I couldn't quite believe that the SUM function would not do the trick. Many thanks again. In article , David Biddulph <groups@[at] writes Abolutely! Well spotted, Roger. -- Stephen White ) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
| Function THat wIll Add COntents Acorss Worksheets | Excel Worksheet Functions | |||
| Function to Return another Worksheets Name | Excel Worksheet Functions | |||
| VLOOKUP Function using multiple worksheets | Excel Discussion (Misc queries) | |||
| Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
Linear Mode
