SUM column based on two dates, SUM function begins at first date which is a variable

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

Formula:

Please Login or Register  to view this content.

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.

Originally Posted by Richard Buttrey

Hi,

Does the attached help?

Yes! Thank you very much.

For others who are looking at similar problems, the solution was a function that looks like this:

Formula:

Please Login or Register  to view this content.

What this formula does is SUM a column of numbers based on criteria, in this case the current date and a date 6 calendar months prior to that date.

The SUMIFS function begins with the range that is to be summed. (L:L)

The $A:$A is the first range to evaluate. The dollar sign means that if you drag this function over multiple cells, that will remain a fixed point for the cell range.

In evaluating $A:$A the cell will be added to the sum total IF it is "greater than or equal to" a specific date. That specific date is defined in the function as &EOMONTH($A1,-6)+1. To explain this, there is a date in cell A1. The EOMONTH function is using that date, identifying the month and then subtracting 6 months (-6). That will result in the last day of the month 7 months prior - which is why there is a "+1"... to add a day and make the result the first day of the calendar month 6 months prior to that date.

The range L:L is then evaluated with a second set of factors. Any cell in L:L is then added to the SUM total, beginning at $A:$A if it meets the criteria of being "less than or equal to" the date specified in A1.

Once all of the cells that do not meet that criteria are identified the remainder are totaled and presented in the cell this formula is in.

Did I get the explanation correct?

Thanks again for the help