+ Reply to Thread
Results 1 to 2 of 2

Assistance incrementing SUM formula row numbers when using reference

  1. #1
    Registered User
    Join Date
    12-30-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    1

    Question Assistance incrementing SUM formula row numbers when using reference

    Hello Everyone,

    I've been having an issue that has been so difficult to solve for me, that I created an account on this site JUST so I could ask it.

    My current predicament is:

    I have an Excel Workbook with 2 sheets: Member Statistics and Team Statistics

    In Member Statistics, I have 3 member names and a column CALLS MADE (Column C; first value stats at C2) for each member for each date, i.e.,

    DATE NAME CALLS MADE
    1-Jan-2014 John 19
    1-Jan-2014 Smith 17
    1-Jan-2014 Holmes 22
    2-Jan-2014 John 12
    2-Jan-2014 Smith 3
    2-Jan-2014 Holmes 16
    3-Jan-2014 John 14
    3-Jan-2014 Smith 15
    3-Jan-2014 Holmes 20

    There are no blank rows. Member names repeat every 3 rows, and dates are incremented by1, every 3 rows.

    On the other sheet (Team Statistics), I have a column CALLS MADE (Column B; first value stats at B2)

    DATE CALLS MADE
    1-Jan-2014 58
    2-Jan-2014 31
    3-Jan-2014 49


    I need a formula that adds the 3 CALLS MADE rows for every date, from the "Member Statistics" sheet, and enters the SUM value in the Calls Made column for that date, in the "Team Statistics" sheet

    The formula that I tried is =SUM('Member Statistics'!C2:C4). This works fine, but I am unable to use the Fill (Down) command since the row numbers fail to increment in groups of 3, and I have too much data to enter the formula in each cell manually.

    How can I make the row numbers increment in groups of 3, i.e.,

    =SUM('Member Statistics'!C2:C4)
    =SUM('Member Statistics'!C5:C7)
    =SUM('Member Statistics'!C8:C10)
    .
    .
    .

    and so on?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Assistance incrementing SUM formula row numbers when using reference

    HI SUM and OFFSET in combination will do what you need
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Incrementing Reference in Formula
    By mc2468 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2014, 03:39 AM
  2. Incrementing cell reference within formula upon a new row insert within a table
    By m_arkle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2014, 04:16 PM
  3. Incrementing the Column Reference in an If Formula
    By HangMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2013, 04:21 PM
  4. Incrementing Reference Numbers
    By SamuelT in forum Excel General
    Replies: 2
    Last Post: 03-11-2009, 07:52 AM
  5. [SOLVED] incrementing formula reference by 7
    By Patti in forum Excel General
    Replies: 2
    Last Post: 01-20-2005, 05:06 PM

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