# Average for the last 12 months

#### pkarri

##### New Member
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.

##### Well-known Member
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

##### Well-known Member
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.

1,081,798
Messages
5,361,373
Members
400,629
Latest member
ganeshkhatri

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...