Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Sum A Group Of 7 Rows On A Second Worksheet

    I hope I can explain this so that someone can assist.

    I have two worksheets, on worksheet 1 I have Column A (dates) and Column B (amounts). On worksheet 2 in cell A1, I need to sum column B rows 1-7 (week’s one totals) from worksheet 1, in cell A2 I need to sum Column B rows 7-14 (week’s two totals). I have almost 5-years’ worth data (approx. 260 weeks).

    I would like to have a formula in cell A1 (worksheet 2) that I can copy/paste for the remaining 259 weeks. I’ve tried using =SUM('Worksheet1'!A1:A7) but when copy it to cell A2 the range (rows) changes to A2:A8, it’s just shifts down one row, not the seven rows (A8:A14) that I was hoping. So I would have to adjust the formula for remaining rows.

    This is time consuming to adjust the formula for remaining cells and it’s not practical because I also have amounts in Columns C – CK on worksheet 1 that I need to sums each week on worksheet 2 as well.

    If I can get the correct formula for cell A1, then I can adjust it for the remaining cells in row 1 (C-CK) and then copy and paste row 1 formulas for the remaining 259 rows.

    Any assistance is greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In Sheet2!A1:
    =SUM(OFFSET(Sheet1!B$1,(ROW()-1)*7,0,7,1))

    This can be copied down the column and also across the rows to pick up the other columns.

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    sbdale (2014-02-16)

Posting Permissions

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