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

[exflyer1996]

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

 

[Toadstool]

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.

 

[exflyer1996]

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.

[TABLE="width: 818"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD] 390,000[/TD]
[TD] 400,000[/TD]
[TD] 400,000[/TD]
[TD] 450,000[/TD]
[TD] 475,000[/TD]
[TD] 475,000[/TD]
[TD] 425,000[/TD]
[TD] 450,000[/TD]
[TD] 450,000[/TD]
[TD] 450,000[/TD]
[TD] 450,000[/TD]
[TD] 515,000[/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD] 144,300[/TD]
[TD] 148,000[/TD]
[TD] 148,000[/TD]
[TD] 166,500[/TD]
[TD] 175,750[/TD]
[TD] 175,750[/TD]
[TD] 157,250[/TD]
[TD] 166,500[/TD]
[TD] 166,500[/TD]
[TD] 166,500[/TD]
[TD] 166,500[/TD]
[TD] 190,550[/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD] 24,871[/TD]
[TD] 22,175[/TD]
[TD] 23,972[/TD]
[TD] 24,871[/TD]
[TD] 33,319[/TD]
[TD] 31,217[/TD]
[TD] 32,268[/TD]
[TD] 33,319[/TD]
[TD] 31,217[/TD]
[TD] 34,370[/TD]
[TD] 30,167[/TD]
[TD] 31,217[/TD]
[/TR]
[TR]
[TD]FOH[/TD]
[TD] 80,227[/TD]
[TD] 73,565[/TD]
[TD] 76,978[/TD]
[TD] 77,993[/TD]
[TD] 84,497[/TD]
[TD] 80,753[/TD]
[TD] 82,379[/TD]
[TD] 79,128[/TD]
[TD] 79,443[/TD]
[TD] 83,595[/TD]
[TD] 80,705[/TD]
[TD] 80,912

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is this what you mean?

Excel Workbook
	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
Sum to Current Month

 

[exflyer1996]

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.

 

[Peter_SSs]

More like this?

Excel Workbook
	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
Data

Excel Workbook
	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
Sum to Current Month

 

[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))

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?

 

[Peter_SSs]

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?

 

[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

 

[Peter_SSs]

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.