Average for the last 12 months

pkarri

New Member
Joined
Nov 4, 2008
Messages
2
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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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
Joined
Mar 10, 2008
Messages
2,638
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top