How do I sum a date specific range of cells?
 

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.

How do I sum a date specific range of cells?
 

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 DataFilterAutoFilter.
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
on OK, the list will be filtered and the average (SUBTOTAL() function) will
show you what you want.


"Gabbon" wrote:

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.

How do I sum a date specific range of cells?
 

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.


"Duke Carey" wrote:

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 DataFilterAutoFilter.
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
on OK, the list will be filtered and the average (SUBTOTAL() function) will
show you what you want.


"Gabbon" wrote:

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.

How do I sum a date specific range of cells?
 

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