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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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, therefore:
>
> =SUM(Sheet1:Sheet5!D50)
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "karatefem" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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, therefore:
> >
> > =SUM(Sheet1:Sheet5!D50)
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "karatefem" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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, therefore:
> >
> > =SUM(Sheet1:Sheet5!D50)
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "karatefem" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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, therefore:
> > >
> > > =SUM(Sheet1:Sheet5!D50)
> > > --
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "karatefem" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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, therefore:
> > >
> > > =SUM(Sheet1:Sheet5!D50)
> > > --
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "karatefem" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
>
>

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" <[email protected]> wrote in message news:[email protected]...
> > 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, therefore:
> > >
> > > =SUM(Sheet1:Sheet5!D50)
> > > --
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "karatefem" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> > > 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, therefore:
> > > >
> > > > =SUM(Sheet1:Sheet5!D50)
> > > > --
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > > "karatefem" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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, therefore:
> > > >
> > > > =SUM(Sheet1:Sheet5!D50)
> > > > --
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > > "karatefem" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> >
> >