Sum cells across variable colums - need formula

S

Schroetke

New Member
Joined
Dec 15, 2010
Messages
17
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Schroetke said:
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.
Click to expand...

Coudlldou post sample of your data?
 
Upvote 0
Excel 2010
ABJKLMNOPQRBFBGBHBIBJ
Ohio
Akron
Monthly P&L
Total Revenues
Total Cost of Sales
Gross Profit (pre COS alloc)
Total SG&A
Direct Interest Allocation
Pre Tax Income
Grand Total PTI

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"] 4 [/TD]
[TD="bgcolor: #FFFF00, align: right"] 16 [/TD]
[TD="bgcolor: #FFFF00, align: right"] 28 [/TD]
[TD="bgcolor: #FFFF00, align: right"] 40 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Totals[/TD]
[TD="align: right"]Jan-13[/TD]
[TD="align: right"]Feb-13[/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Apr-13[/TD]
[TD="align: right"]May-13[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Jul-13[/TD]
[TD="align: right"]Aug-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]Sep-12[/TD]
[TD="bgcolor: #FFFF00, align: right"]Sep-13[/TD]
[TD="bgcolor: #FFFF00, align: right"]Sep-14[/TD]
[TD="bgcolor: #FFFF00, align: right"]Sep-15[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 51,855,882 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 428,561 [/TD]
[TD="align: right"] 1,714,244 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]38,864,283[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 321,192 [/TD]
[TD="align: right"] 1,284,770 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 12,991,599 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 107,369 [/TD]
[TD="align: right"] 429,474 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"] 5,517,531 [/TD]
[TD="align: right"] 7,892 [/TD]
[TD="align: right"] 16,620 [/TD]
[TD="align: right"] 18,541 [/TD]
[TD="align: right"] 143,653 [/TD]
[TD="align: right"] 146,633 [/TD]
[TD="align: right"] 150,111 [/TD]
[TD="align: right"] 152,929 [/TD]
[TD="align: right"] 154,698 [/TD]
[TD="align: right"] 155,066 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"] 177,717 [/TD]
[TD="align: right"] 1,649 [/TD]
[TD="align: right"] 3,473 [/TD]
[TD="align: right"] 3,874 [/TD]
[TD="align: right"] 4,354 [/TD]
[TD="align: right"] 4,977 [/TD]
[TD="align: right"] 5,703 [/TD]
[TD="align: right"] 6,292 [/TD]
[TD="align: right"] 6,662 [/TD]
[TD="align: right"] 6,739 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"] 4,703,558 [/TD]
[TD="align: right"] (9,541)[/TD]
[TD="align: right"] (20,093)[/TD]
[TD="align: right"] (22,415)[/TD]
[TD="align: right"] (148,007)[/TD]
[TD="align: right"] (151,609)[/TD]
[TD="align: right"] (155,815)[/TD]
[TD="align: right"] (159,221)[/TD]
[TD="align: right"] (75,420)[/TD]
[TD="align: right"] 181,957 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"] 4,703,558 [/TD]
[TD="align: right"] (9,541)[/TD]
[TD="align: right"] (20,093)[/TD]
[TD="align: right"] (22,415)[/TD]
[TD="align: right"] (148,007)[/TD]
[TD="align: right"] (151,609)[/TD]
[TD="align: right"] (155,815)[/TD]
[TD="align: right"] (159,221)[/TD]
[TD="align: right"] (75,420)[/TD]
[TD="align: right"] 181,957 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 12 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Cash_Flow (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J5[/TH]
[TD="align: left"]='Timing Assumptions'!K16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K5[/TH]
[TD="align: left"]='Timing Assumptions'!L16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L5[/TH]
[TD="align: left"]='Timing Assumptions'!M16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M5[/TH]
[TD="align: left"]='Timing Assumptions'!N16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N5[/TH]
[TD="align: left"]='Timing Assumptions'!O16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O5[/TH]
[TD="align: left"]='Timing Assumptions'!P16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P5[/TH]
[TD="align: left"]='Timing Assumptions'!Q16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Q5[/TH]
[TD="align: left"]='Timing Assumptions'!R16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]R5[/TH]
[TD="align: left"]='Timing Assumptions'!S16[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J21[/TH]
[TD="align: left"]=IF(MONTH(J5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K21[/TH]
[TD="align: left"]=IF(MONTH(K5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L21[/TH]
[TD="align: left"]=IF(MONTH(L5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M21[/TH]
[TD="align: left"]=IF(MONTH(M5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N21[/TH]
[TD="align: left"]=IF(MONTH(N5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O21[/TH]
[TD="align: left"]=IF(MONTH(O5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P21[/TH]
[TD="align: left"]=IF(MONTH(P5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Q21[/TH]
[TD="align: left"]=IF(MONTH(Q5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]R21[/TH]
[TD="align: left"]=IF(MONTH(R5)=9,12,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BH4[/TH]
[TD="align: left"]=+BG4+12[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BI4[/TH]
[TD="align: left"]=+BH4+12[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BJ4[/TH]
[TD="align: left"]=+BI4+12[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BH5[/TH]
[TD="align: left"]=DATE(YEAR(BG5)+1,MONTH(BG5),DAY(BG5))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BI5[/TH]
[TD="align: left"]=DATE(YEAR(BH5)+1,MONTH(BH5),DAY(BH5))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BJ5[/TH]
[TD="align: left"]=DATE(YEAR(BI5)+1,MONTH(BI5),DAY(BI5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BG4[/TH]
[TD="align: left"]{=MATCH(TRUE,C21:N21=12,0)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]BG5[/TH]
[TD="align: left"]{=INDEX(C5:N18,1,MATCH(TRUE,C21:N21=12,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
159
Domenic
D
E
  • Question Question
Vesting Schedule - Formula/Formatting
Replies
1
Views
102
StephenCrump
S
A
  • Solved
Conditional Formatting for rows of empty cells corresponding with dates between
Replies
4
Views
467
avril18
A
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
476
alansidman
A
E
  • Question Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
318
ExcelenteNovel
E

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

Share this page

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