# Sum cells across variable colums - need formula

#### Schroetke

##### New Member
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.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?

Excel 2010
ABJKLMNOPQRBFBGBHBIBJ
1Ohio
2Akron
3Monthly P&L
4 4 16 28 40
5TotalsJan-13Feb-13Mar-13Apr-13May-13Jun-13Jul-13Aug-13Sep-13Sep-12Sep-13Sep-14Sep-15
6Total Revenues 51,855,882 - - - - - - - 428,561 1,714,244
7
8Total Cost of Sales38,864,283 - - - - - - - 321,192 1,284,770
9
10Gross 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
14Direct Interest Allocation 177,717 1,649 3,473 3,874 4,354 4,977 5,703 6,292 6,662 6,739
15
16Pre 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

</tbody>
Cash_Flow (2)

Worksheet Formulas
CellFormula
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))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
BG4{=MATCH(TRUE,C21:N21=12,0)}
BG5{=INDEX(C5:N18,1,MATCH(TRUE,C21:N21=12,0))}

</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.

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.

Replies
0
Views
1K
Replies
4
Views
248
Replies
1
Views
243
Replies
3
Views
67
Replies
6
Views
387

### Forum statistics

1,203,462
Messages
6,055,565
Members
444,799
Latest member
CraigCrowhurst ### 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.

### Which adblocker are you using?    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

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