| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Formula for adding several worksheet totals
I have an expense statement that has 5 worksheets (one for each potential
week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#2
|
|||
|
|||
|
Are the totals of each individual sheet in exactly the same cell on each
sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#3
|
|||
|
|||
|
I tried pasting the formula in the cell I need the total to be - I2 at the
top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#4
|
|||
|
|||
|
Can't think of any if the leftmost sheetname is Sheet1 of those to be totaled
and the rightmost sheetname is Sheet50. Sometimes people use empty sheets for the two ends just so they can insert any number of sheets in between. You will find information about 3D references in help i.e. SUM 3D the topic is: Refer to the same cell or range on multiple sheets by using a 3-D reference --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#5
|
|||
|
|||
|
Sounds like your sheets might not be named *or* spelled exactly as it is in
the formula! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#6
|
|||
|
|||
|
So the formula =SUM(Sheet1:Sheet5!D50)
Should read =SUM(Week1:Week5!I21) That is stating that I want Worksheet named Week 1 through Week 5 to add all of the cells in I21 on each sheet. correct? r "RagDyer" wrote: Sounds like your sheets might not be named *or* spelled exactly as it is in the formula! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#7
|
|||
|
|||
|
This is the error I am getting (I corrected a field it should have been I22
in each sheet) =SUM(week1:'[week5]Week 1 '!I22) #Name? "RagDyer" wrote: Sounds like your sheets might not be named *or* spelled exactly as it is in the formula! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#8
|
|||
|
|||
|
HEY FIGURED IT OUT!!!!
=SUM(I22,'Week 2'!I22,'Week 3'!I22,'Week 4'!I22,'Week 5'!I22) "David McRitchie" wrote: Can't think of any if the leftmost sheetname is Sheet1 of those to be totaled and the rightmost sheetname is Sheet50. Sometimes people use empty sheets for the two ends just so they can insert any number of sheets in between. You will find information about 3D references in help i.e. SUM 3D the topic is: Refer to the same cell or range on multiple sheets by using a 3-D reference --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#9
|
|||
|
|||
|
You can also do that as a 3D reference, as suggested, if the order of the sheets
left to right is 'Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5' all sheets wanted are on contiguous tabs =SUM('Week 1':'Week 5'!I22 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "karatefem" wrote in message ... HEY FIGURED IT OUT!!!! =SUM(I22,'Week 2'!I22,'Week 3'!I22,'Week 4'!I22,'Week 5'!I22) "David McRitchie" wrote: Can't think of any if the leftmost sheetname is Sheet1 of those to be totaled and the rightmost sheetname is Sheet50. Sometimes people use empty sheets for the two ends just so they can insert any number of sheets in between. You will find information about 3D references in help i.e. SUM 3D the topic is: Refer to the same cell or range on multiple sheets by using a 3-D reference --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
|
#10
|
|||
|
|||
|
First of all, these *are* sheets in the same workbook ... right?
Next, read this old post: http://tinyurl.com/dhoaz BTW, are there spaces between WEEK and the number 1? Looks like you put *2* spaces between the name and the number in one of your examples! What's chances that you made that mistake when you named your sheets in the first place? That is, put differing spaces between the name and the number in the actual tabs themselves. LOOK, with this formula, the *only* thing that matters is that the first and the last sheet match *exactly* in both the formula and the sheet tabs. I duplicated the error you were getting by putting extra spaces between the "week" and the number "5". So, check everything, and try again. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... This is the error I am getting (I corrected a field it should have been I22 in each sheet) =SUM(week1:'[week5]Week 1 '!I22) #Name? "RagDyer" wrote: Sounds like your sheets might not be named *or* spelled exactly as it is in the formula! -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "karatefem" wrote in message ... I tried pasting the formula in the cell I need the total to be - I2 at the top of sheet one-, change D50 to I21 which is the same cell on each spreadsheet that I want to add up, however it gives me an error #NAME? Am I missing a step? kf "RagDyer" wrote: Are the totals of each individual sheet in exactly the same cell on each sheet? If so, say total in D50, therefo =SUM(Sheet1:Sheet5!D50) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "karatefem" wrote in message ... I have an expense statement that has 5 worksheets (one for each potential week of the month). I want to add a formula to the first worksheet that adds the totals of all the worksheets together. Anyone have a simple formula I can copy and paste? r |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating formula with link to another worksheet using vlookup | Links and Linking in Excel | |||
| help keeping the worksheet names within formula | Excel Worksheet Functions | |||
| How do I get the formula to show the result on the worksheet? | Excel Discussion (Misc queries) | |||
| need a formula to copy entire row to next worksheet | Excel Worksheet Functions | |||
| Replace worksheet name in formula linked to a different workbook | Excel Discussion (Misc queries) | |||
Linear Mode
