Results 1 to 10 of 10

Thread: How do you sum a row of data that will increase by 1 cell each month?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2009
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do you sum a row of data that will increase by 1 cell each month?

    I am comparing year-to-date financial statements with Budget and prior year. I want to return the sum of the row that starts with January and increases by 1 each month. I was thinking it was an INDEX function that could start the sum in the first column and then run over the specified number of rows 2 through 12.

    EXAMPLE: The row title Revenue is in cell A5. January data is in B5 and the data runs through December in M5. In February I want it to run 2 columns, March 3, etc.

    Thank you

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Could you explain further. Preferably with a worked example.

    You talk of rows 2 through 12 but then say January is in B5 through December in M5, but that means your months are in columns.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular
    Join Date
    May 2009
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    You are right. I should have said columns B through M. "Revenue" is in A5. B5 has the amount for January. Revenue data runs from B5 through M5. Each month, the number of columns that I want to sum needs to increase by 1. In February I want B through C. In April B through D.

    JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
    Revenue 390,000 400,000 400,000 450,000 475,000 475,000 425,000 450,000 450,000 450,000 450,000 515,000
    Material 144,300 148,000 148,000 166,500 175,750 175,750 157,250 166,500 166,500 166,500 166,500 190,550
    Labor 24,871 22,175 23,972 24,871 33,319 31,217 32,268 33,319 31,217 34,370 30,167 31,217
    FOH 80,227 73,565 76,978 77,993 84,497 80,753 82,379 79,128 79,443 83,595 80,705 80,912

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Is this what you mean?

    Sum to Current Month

    ABCDEFGHIJKLMNO
    1 JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC Sum to current Month
    2Revenue390,000400,000400,000450,000475,000475,000425,000450,000450,000450,000450,000515,000 3,915,000
    3Material144,300148,000148,000166,500175,750175,750157,250166,500166,500166,500166,500190,550 1,448,550
    4Labor24,87122,17523,97224,87133,31931,21732,26833,31931,21734,37030,16731,217 257,229
    5FOH80,22773,56576,97877,99384,49780,75382,37979,12879,44383,59580,70580,912 714,963

    Spreadsheet Formulas
    CellFormula
    O2=SUM(B2:INDEX(B2:M2,MONTH(TODAY())))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    May 2009
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Close. I failed to explain an important part of this. I have all of the raw data on a tab, with row titles in column A. On a second tab, I used a vlookup formula to pull in a certain month's data for comparison of Budget, Actual, and Prior Year. Works fine. I wanted to use that vlookup mindset, and pull in year-to-date data. I wanted to do something like a vlookup, but one that brought back the SUM of a set number of cells. My current work around is that I created 12 additional columns of data out to the right on the raw data tab. As you move to the right the SUM formulas sum another month. I then used another vlookup, and pull the cumulative cell.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    More like this?

    Data

    ABCDEFGHIJKLM
    1 JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
    2Revenue390,000400,000400,000450,000475,000475,000425,000450,000450,000450,000450,000515,000
    3Material144,300148,000148,000166,500175,750175,750157,250166,500166,500166,500166,500190,550
    4Labor24,87122,17523,97224,87133,31931,21732,26833,31931,21734,37030,16731,217
    5FOH80,22773,56576,97877,99384,49780,75382,37979,12879,44383,59580,70580,912


    Excel tables to the web >> Excel Jeanie HTML 4


    Sum to Current Month

    AB
    1 Sum to current Month
    2Material1,448,550
    3Revenue3,915,000
    4FOH714,963
    5Labor257,229

    Spreadsheet Formulas
    CellFormula
    B2=SUM(INDEX(Data!B$2:B$5,MATCH(A2,Data!A$2:A$5,0)):INDEX(Data!B$2:M$5,MATCH(A2,Data!A$2:A$5,0),9))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    May 2009
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    I think it can be simpler. I used the formula below to return 10 columns of Revenue data. I can/will change the 10 to a cell reference, so that I can return whichever cells I want.

    =SUM(C9:INDEX(C9:N9,10))

    Given that I have raw data on 1 tab that I want to lookup and return the SUM of data referenced in that formula, can I do some type of lookup or nested index?

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Quote Originally Posted by exflyer1996 View Post
    I think it can be simpler. I used the formula below to return 10 columns of Revenue data. I can/will change the 10 to a cell reference, so that I can return whichever cells I want.

    =SUM(C9:INDEX(C9:N9,10))
    You can certainly do that if you know which row to look at.
    You will see that with my sample data "Revenue", "Material" etc are on different rows in the Data sheet and the formula sheet, so I made it so that the formula would find the correct row no mater what order they appear in in the two sheets.



    Quote Originally Posted by exflyer1996 View Post
    Given that I have raw data on 1 tab that I want to lookup and return the SUM of data referenced in that formula, can I do some type of lookup or nested index?
    Isn't that what we are already doing here?

    If you mean something different, can you be more specific about what you have, where and what you are trying to achieve?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular
    Join Date
    May 2009
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Hi Peter
    Your =SUM(INDEX(Data!B$2:B$5,MATCH(A2,Data!A$2:A$5,0)):INDEX(Data!B$2:M$5,MATCH(A2,Data!A$2:A$5,0),9)) formula did the trick. Thank you

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How do you sum a row of data that will increase by 1 cell each month?

    Quote Originally Posted by exflyer1996 View Post
    Hi Peter
    Your =SUM(INDEX(Data!B$2:B$5,MATCH(A2,Data!A$2:A$5,0)):INDEX(Data!B$2:M$5,MATCH(A2,Data!A$2:A$5,0),9)) formula did the trick. Thank you
    You're welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •