Formula for adding several worksheet totals

[karatefem]

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

 

[RagDyer]

Are the totals of each individual sheet in exactly the same cell on each
sheet?

If so, say total in D50, therefore:

=SUM(Sheet1:Sheet5!D50)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[karatefem]

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

 

[David McRitchie]

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

 

[RagDyer]

Sounds like your sheets might not be named *or* spelled exactly as it is in
the formula!

 

[karatefem]

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

 

[karatefem]

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?

 

[karatefem]

HEY FIGURED IT OUT!!!!
=SUM(I22,'Week 2'!I22,'Week 3'!I22,'Week 4'!I22,'Week 5'!I22)

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

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

 

[David McRitchie]

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

HEY FIGURED IT OUT!!!!
=SUM(I22,'Week 2'!I22,'Week 3'!I22,'Week 4'!I22,'Week 5'!I22)

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

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

:

Are the totals of each individual sheet in exactly the same cell on each
sheet?

If so, say total in D50, therefore:

=SUM(Sheet1:Sheet5!D50)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


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

 

[RagDyer]

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!
==============================================

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?

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

!

--------------------------------------------------------------------------

-