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

exflyer1996

Board Regular
Joined
May 25, 2009
Messages
123
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.

JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
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


<colgroup><col><col span="12"></colgroup><tbody>
</tbody>
 
Upvote 0
Is this what you mean?

Excel Workbook
ABCDEFGHIJKLMNO
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECSum to current Month
2Revenue390,000400,000400,000450,000475,000475,000425,000450,000450,000450,000450,000515,0003,915,000
3Material144,300148,000148,000166,500175,750175,750157,250166,500166,500166,500166,500190,5501,448,550
4Labor24,87122,17523,97224,87133,31931,21732,26833,31931,21734,37030,16731,217257,229
5FOH80,22773,56576,97877,99384,49780,75382,37979,12879,44383,59580,70580,912714,963
Sum to Current Month
 
Upvote 0
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.
 
Upvote 0
More like this?

Excel Workbook
ABCDEFGHIJKLM
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
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
Data



Excel Workbook
AB
1Sum to current Month
2Material1,448,550
3Revenue3,915,000
4FOH714,963
5Labor257,229
Sum to Current Month
 
Upvote 0
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?
 
Upvote 0
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.



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?
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top