Sum cells across variable colums - need formula

[Schroetke]

I'm stumped and need a hand with this one.
My spreadsheet is a production schedule. Column A contains text labels (sales, revenue, gross margin for example). Row 5 for Columns B through AX are dates (the end day of each month) that are driven by a formula based on the "schedule start date" that is stored elsewhere. So the first date in cell B5 is variable - it could be April 30, 2012 or June 30, 2013 or anything else - depending on when the user determines the schedule should begin. The data begins in cell B6 and continues through AX 20. So the data range is sales, revenue, gross margin, etc by month.

To the right of the primary data table, I want to sum the data for each row by fiscal year (September in my case). I've used Match to determine the column that corresponds with the end of the fiscal year. So, for example, the end of the first fiscal year is in column 4, and the end of the second fiscal year is in column 16. Given that I know the column numbers of the 2 fiscal year ends, what formula is the most efficient to sum the data for (in this example) columns 5 through 16 for a given row? [Extending the initial example, I want total Sales, Revenue, Gross Margin, etc. for each fiscal year beginning in cell BA6].

Help is much appreciated.

 

[Robert Mika]

I'm stumped and need a hand with this one.
My spreadsheet is a production schedule. Column A contains text labels (sales, revenue, gross margin for example). Row 5 for Columns B through AX are dates (the end day of each month) that are driven by a formula based on the "schedule start date" that is stored elsewhere. So the first date in cell B5 is variable - it could be April 30, 2012 or June 30, 2013 or anything else - depending on when the user determines the schedule should begin. The data begins in cell B6 and continues through AX 20. So the data range is sales, revenue, gross margin, etc by month.

To the right of the primary data table, I want to sum the data for each row by fiscal year (September in my case). I've used Match to determine the column that corresponds with the end of the fiscal year. So, for example, the end of the first fiscal year is in column 4, and the end of the second fiscal year is in column 16. Given that I know the column numbers of the 2 fiscal year ends, what formula is the most efficient to sum the data for (in this example) columns 5 through 16 for a given row? [Extending the initial example, I want total Sales, Revenue, Gross Margin, etc. for each fiscal year beginning in cell BA6].

Help is much appreciated.

Coudlldou post sample of your data?

 

[Schroetke]

Excel 2010

	A	B	J	K	L	M	N	O	P	Q	R	BF	BG	BH	BI	BJ
1	Ohio
2	Akron
3	Monthly P&L
4													4	16	28	40
5		Totals	Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13		Sep-12	Sep-13	Sep-14	Sep-15
6	Total Revenues	51,855,882	-	-	-	-	-	-	-	428,561	1,714,244
7
8	Total Cost of Sales	38,864,283	-	-	-	-	-	-	-	321,192	1,284,770
9
10	Gross Profit (pre COS alloc)	12,991,599	-	-	-	-	-	-	-	107,369	429,474
11
12	Total SG&A	5,517,531	7,892	16,620	18,541	143,653	146,633	150,111	152,929	154,698	155,066
13
14	Direct Interest Allocation	177,717	1,649	3,473	3,874	4,354	4,977	5,703	6,292	6,662	6,739
15
16	Pre Tax Income	4,703,558	(9,541)	(20,093)	(22,415)	(148,007)	(151,609)	(155,815)	(159,221)	(75,420)	181,957
17
18	Grand Total PTI	4,703,558	(9,541)	(20,093)	(22,415)	(148,007)	(151,609)	(155,815)	(159,221)	(75,420)	181,957
19
20
21			-	-	-	-	-	-	-	-	12

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Cash_Flow (2)​

Worksheet Formulas Cell Formula J5 ='Timing Assumptions'!K16 K5 ='Timing Assumptions'!L16 L5 ='Timing Assumptions'!M16 M5 ='Timing Assumptions'!N16 N5 ='Timing Assumptions'!O16 O5 ='Timing Assumptions'!P16 P5 ='Timing Assumptions'!Q16 Q5 ='Timing Assumptions'!R16 R5 ='Timing Assumptions'!S16 J21 =IF(MONTH(J5)=9,12,0) K21 =IF(MONTH(K5)=9,12,0) L21 =IF(MONTH(L5)=9,12,0) M21 =IF(MONTH(M5)=9,12,0) N21 =IF(MONTH(N5)=9,12,0) O21 =IF(MONTH(O5)=9,12,0) P21 =IF(MONTH(P5)=9,12,0) Q21 =IF(MONTH(Q5)=9,12,0) R21 =IF(MONTH(R5)=9,12,0) BH4 =+BG4+12 BI4 =+BH4+12 BJ4 =+BI4+12 BH5 =DATE(YEAR(BG5)+1,MONTH(BG5),DAY(BG5)) BI5 =DATE(YEAR(BH5)+1,MONTH(BH5),DAY(BH5)) BJ5 =DATE(YEAR(BI5)+1,MONTH(BI5),DAY(BI5)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula BG4 {=MATCH(TRUE,C21:N21=12,0)} BG5 {=INDEX(C5:N18,1,MATCH(TRUE,C21:N21=12,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this makes sense. I'm using the formula in R21 to determine if the month in each column is September (the end of the fiscal year). The formulas in BG4 through BJ5 set the headers for the new data table i want to create. In this example, since the first FY is in column 4 and the second FY is in column 16, I am looking for an efficient formula to add all numbers for each row between columns 5 and 16 for column BG, 17 and 28 for column BH etc.

 

[Schroetke]

SOLVED
I played with this for too long, but it provided a deeper understanding of the Index and Match functions than I had previously. I still do not know if my approach is the most efficient, but it has not slowed things down appreciably. The formula I used (in BH6 for instance) is =SUM(INDEX(CF_DATA,ROW()-4,BH$4+1):INDEX(CF_DATA,ROW()-4,BV$4)). Where CF_Data is the primary data table beginning in row 5. I could likely incorporate the Match in row 5 into this formula, but not sure that would be any more efficient than I have it.
Anyway - thanks all for your consideration.