+ Reply to Thread
Results 1 to 10 of 10

Formula for adding several worksheet totals

  1. #1
    karatefem
    Guest

    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. #2
    RagDyer
    Guest

    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

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

    can
    > copy and paste?
    > r



  3. #3
    karatefem
    Guest

    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

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

    > can
    > > copy and paste?
    > > r

    >
    >


  4. #4
    David McRitchie
    Guest

    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

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

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

    > >
    > >




  5. #5
    RagDyer
    Guest

    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
    > > adds
    > > > the totals of all the worksheets together. Anyone have a simple

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

    > >
    > >



  6. #6
    karatefem
    Guest

    Re: Formula for adding several worksheet totals

    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" <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
    > > > adds
    > > > > the totals of all the worksheets together. Anyone have a simple

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

    >
    >


  7. #7
    karatefem
    Guest

    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
    > > > adds
    > > > > the totals of all the worksheets together. Anyone have a simple

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

    >
    >


  8. #8
    karatefem
    Guest

    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
    > > > adds
    > > > > the totals of all the worksheets together. Anyone have a simple formula I
    > > > can
    > > > > copy and paste?
    > > > > r
    > > >
    > > >

    >
    >
    >


  9. #9
    David McRitchie
    Guest

    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
    > > > > adds
    > > > > > the totals of all the worksheets together. Anyone have a simple formula I
    > > > > can
    > > > > > copy and paste?
    > > > > > r
    > > > >
    > > > >

    > >
    > >
    > >




  10. #10
    RagDyer
    Guest

    Re: Formula for adding several worksheet totals

    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" <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
    > > > > adds
    > > > > > the totals of all the worksheets together. Anyone have a simple

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

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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