How do I sum a date specific range of cells?

[Guest]

Hi,
There's got to be a way to do this:
I have several years expenses listed with their dates in Column A, and their
data in Column B.
However, I would like to view the three-monthly average expense for
differing three month ranges.
Also I would like to calculate this average from different days of the month
(eg. if today were the 12th of April, I would like to make the calculation
from december 12th to March 12th.)
Any suggestions how to do this?
Thanks.

 

[Guest]

Off to the side of your data range enter the formula

=subtotal(1, entire column of VALUES)

This will give you the average of the filtered values - since they're not
yet filtered it's the average of the whole list.

Now, select any cell in your data range and go to Data>Filter>AutoFilter.
Click on the down arrow at the top of your Date column and choose Custom. In
the dialog that appears Choose Greater than and enter your starting date, &
on the next line choose Less than and enter your ending date. When you click

 

[Guest]

Thanks for the reply.

However, I have a number of average calculations that I wish to perform (eg.
last 3 months, the 3 months beginning before that, last 6 months, last year)

For this reason, using the "filter" option is not ideal (I would have to
re-filter every time I wish to see the averages, and I would have to record
the averages manually).

Is there a way to calculate the average monthly expense for column B
according to specific date restrictions that are placed upon the date column
(column A): eg. the last three months, or the three months preceding this?

Thanks again.

 

[Guest]

For two columns (Expense_column and Date_column), I ended up using the
forumla for a range four months prior to the latest date entered
max(date_column):

=(SUMIF(Date_column,">="&EDATE(MAX(Date_column),-4),Expense_column))/4