I appreciate your patience as I work through explaining the scenario - I do not know if I am trying to create a three dimensional array or if it can be solved.
The spreadsheet is being used to SUM a column between two specific dates. Let's assume in the actual spreadsheet, Column A contains the list of dates beginning 1/1/2012 and continues on forever, although I am using other dates in the table below.
|
Column A Current Date |
Column B |
Column C |
Column D |
Column E |
Row 1 |
2/27/2012 |
|
|
|
Row 2 |
2/28/2012 |
|
|
|
Row 3 |
3/1/2012 |
|
|
|
Row 4 |
3/2/2012 |
|
|
|
Row 5 |
3/3/2012 |
|
|
|
If the current date is 3/3/2012 I need to begin the summation based on the first day of the previous month; in this example that would be 2/1/2012. So, in column B I used the DATE function
which should generate:
|
Column A - Current Date |
Column B - Previous Month |
Column C |
Column D |
Column E |
Row 1 |
2/27/2012 |
1/1/2012 |
|
|
Row 2 |
2/28/2012 |
1/1/2012 |
|
|
Row 3 |
3/1/2012 |
2/1/2012 |
|
|
Row 4 |
3/2/2012 |
2/1/2012 |
|
|
Row 5 |
3/3/2012 |
2/1/2012 |
|
|
For the next step, lets assume Column C has the Data values which need to be totaled - they begin 1/1/2012 and run through the current date.
|
Column A - Current Date |
Column B - Previous Month |
Column C - units |
Column D |
Column E |
Row 1 |
2/27/2012 |
1/1/2012 |
1 |
|
Row 2 |
2/28/2012 |
1/1/2012 |
2 |
|
Row 3 |
3/1/2012 |
2/1/2012 |
0 |
|
Row 4 |
3/2/2012 |
2/1/2012 |
3 |
|
Row 5 |
3/3/2012 |
2/1/2012 |
2 |
|
What I need to solve is Column D. Column D needs to total the values from Column C based on the date range generated in Column B. So, for example D1 would be totaling the cells ranging 1/1/2012 through 2/27/2012. D2 would generate the total units from 1/1/2012 through 2/28/2012. D3 needs to generate the units over the range 2/1/2012 through 3/1/2012.
As you can see, this is presenting a challenge to me because the range selected changes based on the date. I also do not know many advanced functions and their syntax to make functions work properly.
If the end result needs multiple formulas in multiple cells to make the end result, that is perfectly fine. This is a personal workbook.
Attached is the workbook for the actual problem. I am trying to solve Columns AI and AJ on the 'Flight Time' workbook. AI and AJ are based on the sum of the numbers in column L and M respectively. AI and AJ need to SUM column L and M based on the date in Column A beginning from the first of the month, six months previous. (A date of 6/5/13 would sum from 1/1/13 through 6/5/13 and a date of 9/28/13 would need a sum from 4/1/13 through 9/28/13.)
If I can add additional information to assist in the solution please post. Thanks again.
Bookmarks