+ Reply to Thread
Results 1 to 5 of 5

skipping sums across worksheets

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    2

    skipping sums across worksheets

    I have been combing the forums for an answer but feel there may be an easier way to solve my problem without learning arrays, etc. (hopefully!). I have some data on one sheet and I am trying to represent the sum of data on another sheet. I realize for one instance of summing I could complete by writing "=SUM(data!B3:B4)." However, if I want the cell below the previous one to indicate "=SUM(data!B5:B6)." Here is where I run into my dilemma. When I drag for formula down to populate the column it doesn't skip what has been summed already and would provide =SUM(data!B4:B5), then =SUM(data!B5:B6), etc. How does the logic change for including the sum of three or four at a time without the same overlapping problem when the formula is dragged down? Any help would be appreciated; many thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: skipping sums across worksheets

    Try this in B3 and filled down

    =SUM(OFFSET(data!$B$3:$B$4,(ROWS(A$1:A1)-1)*2,0))

    Don't change the A$1:A1 reference, it is not related to your data.
    The *2 represents the interval.

    So if you wanted to do every 3 rows, adjust the initial range to B3:B5 and change the *2 to *3

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: skipping sums across worksheets

    An alternative

    =SUM(INDIRECT("Data!B" &ROW()+((ROW(A2)-2)*1)&":B"&ROW()+((ROW(A2)-1)*1)))

  4. #4
    Registered User
    Join Date
    04-08-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: skipping sums across worksheets

    Thank you Jonmo1 and JieJenn! You have just saved me hours of manual input!

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: skipping sums across worksheets

    Great, glad to help out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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