Average for the last 12 months

[pkarri]

Hi,
I've been trying to do this for a day now and have searched the boards, but couldn't find a satisfactory solution. I'm hoping someone can help.

I have daily sales information going back several years. The data is organized as follows:

Date Sales
11/4/08 $aaa
11/3/08 $bbb
10/31/08 $xxx
10/30/08 $yyy
:
:
:
6/12/02 $zzz

I need to summarize this as follows:

Month Average Sales
Nov-08
Oct-08
Sep-08
:
:
:
Dec-07

The data keeps getting updated daily, but the summary should be for the last 12 months, including the current (partial) month. There is no data for weekends and holidays. I can do this manually and update the sum() range daily for the past 12 months, but is there an easier way to do this?

Appreciate any suggestions.

 

[crimson_b1ade]

Use the SUMIF function. You need to add another column next to your data that you're going to sum to strip out the days from the month(let's say this is in column c) use formula =Month(A1)&Year(A1). Then you can use the sumif function. =SUMIF(C:C,month(A1)&Year(A1),B:B)

 

[mvptomlinson]

Another option would be to use a pivot table and group the dates by month.

Along those same lines, you could create a third column (let's say C1:C10, assuming your original data is in A1:A10 and B1:B10) using the formula:

=TEXT(A1,"mmm-dd")

Use that in C1 and fill down to C10. Then create a pivot table off of all three columns, and group by column C instead of by column A.

 

[pkarri]

Thanks crimson b1ade and mvptomlinson for your suggestions.