# Formula for adding several worksheet totals

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. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message
news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> 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

> the totals of all the worksheets together. Anyone have a simple formula I

can
> copy and paste?
> r

3. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message
> news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > 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

> > the totals of all the worksheets together. Anyone have a simple formula I

> can
> > copy and paste?
> > r

>
>

4. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> 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" <karatefem@discussions.microsoft.com> wrote in message
> > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > 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

> > > the totals of all the worksheets together. Anyone have a simple formula I

> > can
> > > copy and paste?
> > > r

> >
> >

5. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message
news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> 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" <karatefem@discussions.microsoft.com> wrote in message
> > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > 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
> > > the totals of all the worksheets together. Anyone have a simple

formula I
> > can
> > > copy and paste?
> > > r

> >
> >

6. ## Re: Formula for adding several worksheet totals

So the formula =SUM(Sheet1:Sheet5!D50)

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" <karatefem@discussions.microsoft.com> wrote in message
> news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> > 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" <karatefem@discussions.microsoft.com> wrote in message
> > > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > > 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
> > > > the totals of all the worksheets together. Anyone have a simple

> formula I
> > > can
> > > > copy and paste?
> > > > r
> > >
> > >

>
>

7. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message
> news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> > 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" <karatefem@discussions.microsoft.com> wrote in message
> > > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > > 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
> > > > the totals of all the worksheets together. Anyone have a simple

> formula I
> > > can
> > > > copy and paste?
> > > > r
> > >
> > >

>
>

8. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> > 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" <karatefem@discussions.microsoft.com> wrote in message
> > > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > > 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
> > > > the totals of all the worksheets together. Anyone have a simple formula I
> > > can
> > > > copy and paste?
> > > > r
> > >
> > >

>
>
>

9. ## Re: Formula for adding several worksheet totals

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" <karatefem@discussions.microsoft.com> wrote in message news:F318BC45-94D2-480A-B090-962ED60CD263@microsoft.com...
> 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" <karatefem@discussions.microsoft.com> wrote in message news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> > > 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" <karatefem@discussions.microsoft.com> wrote in message
> > > > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > > > 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
> > > > > the totals of all the worksheets together. Anyone have a simple formula I
> > > > can
> > > > > copy and paste?
> > > > > r
> > > >
> > > >

> >
> >
> >

10. ## Re: Formula for adding several worksheet totals

First of all, these *are* sheets in the same workbook ... right?

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" <karatefem@discussions.microsoft.com> wrote in message
news:17C0212A-9368-4AAA-9B43-2D460E48D70B@microsoft.com...
> 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" <karatefem@discussions.microsoft.com> wrote in message
> > news:F0436B75-5410-4F39-9C93-9FA741B5F9A9@microsoft.com...
> > > 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" <karatefem@discussions.microsoft.com> wrote in message
> > > > news:4E754B6C-8842-4A2C-8F94-A04F9B1FB160@microsoft.com...
> > > > > 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
> > > > > the totals of all the worksheets together. Anyone have a simple

> > formula I
> > > > can
> > > > > copy and paste?
> > > > > r
> > > >
> > > >

> >
> >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1