Copying down sum formula with non equal ranges
Results 1 to 5 of 5

Thread: Copying down sum formula with non equal ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2017
    Location
    Nashville
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying down sum formula with non equal ranges

    I have two columns, one with dates and one with values. Such as:

    8/4/19 1
    8/5/19 5
    8/6/19 4
    8/7/19 4
    8/8/19 7
    8/9/19 4
    8/10/19 4
    8/11/19 4
    8/12/19 3
    8/13/19 4
    8/14/19 3
    8/15/19 2
    8/16/19 4
    8/17/19 4

    In a separate cell I want to sum up the first week, i.e. =sum(b1:b7). I want to fill that formula down one row to capture the second week. But of course if I fill down one row it will change the formula to =sum(b2:b8) rather than my desired value, =sum(b8:b14). Any ideas on how to approach this?
    Last edited by jbenfleming; Aug 8th, 2019 at 12:48 PM.

  2. #2
    New Member
    Join Date
    Mar 2017
    Location
    Nashville
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying down sum formula with non equal ranges

    FYI,

    I know I could accomplish this with VBA, but I am considering that a last case scenario.

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,390
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copying down sum formula with non equal ranges

    Sheet2

    ABC
    18/4/2019129
    28/5/2019524
    38/6/20194
    48/7/20194
    58/8/20197
    68/9/20194
    78/10/20194
    88/11/20194
    98/12/20193
    108/13/20194
    118/14/20193
    128/15/20192
    138/16/20194
    148/17/20194

    Spreadsheet Formulas
    CellFormula
    C1=SUM(OFFSET(B$1,(ROW()-1)*7,0,7,1))


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    New Member
    Join Date
    Mar 2017
    Location
    Nashville
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying down sum formula with non equal ranges

    Perfect. Thank you!

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,390
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copying down sum formula with non equal ranges

    Quote Originally Posted by jbenfleming View Post
    Perfect. Thank you!
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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