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

1. ## 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. ## 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.

3. ## 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. ## 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

 A B C D E F G H I J K L M N O 1 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Sum to current Month 2 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 3,915,000 3 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 1,448,550 4 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 257,229 5 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 714,963

 Cell Formula O2 =SUM(B2:INDEX(B2:M2,MONTH(TODAY())))

Excel tables to the web >> Excel Jeanie HTML 4

5. ## 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. ## Re: How do you sum a row of data that will increase by 1 cell each month?

More like this?

Data

 A B C D E F G H I J K L M 1 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 2 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 3 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 4 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 5 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

Excel tables to the web >> Excel Jeanie HTML 4

Sum to Current Month

 A B 1 Sum to current Month 2 Material 1,448,550 3 Revenue 3,915,000 4 FOH 714,963 5 Labor 257,229

 Cell Formula 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

7. ## 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. ## Re: How do you sum a row of data that will increase by 1 cell each month?

Originally Posted by exflyer1996
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.

Originally Posted by exflyer1996
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?

9. ## 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. ## Re: How do you sum a row of data that will increase by 1 cell each month?

Originally Posted by exflyer1996
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.