Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running sum for last three months data (2003)

    I have a workbook set up with 13 worksheets - a summary sheet at the front, then one worksheet for each month of the year. As I go through the year, I enter data onto the relevant month's worksheet. On the summary sheet, I'd like to see the sum of the previous three months only, such that it changes as I enter the next month's data. Can anyone please advise me the best way to write a formula to achieve this? Any help will be very gratefully received. Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running sum for last three months data (2003)

    Instead of having separate sheets for each month of the year, I'd use one worksheet for the data, with an extra date column if you don't have one already. You can then use a SUMPRODUCT or SUM(IF(...)) formula on the summary sheet to total data for the last three months.

  3. #3
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running sum for last three months data (2003)

    Unfortunately, the spreadsheet is quite complex and needs to be set up on separate worksheets. It's been like that for ever and my colleague doesn't want to change the layout. Can you think of anything else I can do to get the running sum of the previous three months or am I just going to have to adjust the formula manually each time?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running sum for last three months data (2003)

    We'd have to know more about the setup of the workbook.

  5. #5
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running sum for last three months data (2003)

    OK, I'm attaching a simple example. In cell B5 on the Summary Sheet, I have the formula =SUM(Feb:Apr!B5). At the moment, there are no figures in the May sheet (or for the remainder of the year), hence the formula for the last three months goes from Feb-Apr. What I'd like to be able to do is when I put the figures in for May, the formula should automatically adjust so that it says Mar:May rather than Feb:Apr. So basically, what I'm trying to get on the summary sheet is the last three months that have data on the worksheet. Does that make sense?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running sum for last three months data (2003)

    Try the attached version. It's not very elegant, but in a limited test it appears to work.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running sum for last three months data (2003)

    BTW you can hide the columns with the auxiliary results in the workbook that I attached. I'd leave them visible while you're testing it, though.

  8. #8
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running sum for last three months data (2003)

    Well, it certainly seems to work. Thank you so much. I'll now go away and try to adapt the formula for our own spreadsheet.

Posting Permissions

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