+ Reply to Thread
Results 1 to 5 of 5

Sales daily sales data to be aggregated into monthly data (not using pivot table)

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Question Sales daily sales data to be aggregated into monthly data (not using pivot table)

    Hi All,

    I am facing a problem I cannot solve with Excel 2010 and I have searched all over for the answer.

    I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a seperate column.

    For example, I would like to turn this:

    3/2/2005 $xxxx
    3/5/2005 $xxxx
    3/20/2005 $xxxx
    4/2/2005 $xxxx
    4/10/2005 $xxxx

    Into this:
    March 2005- $xxxx (monthly average)
    April 2005- $xxxx (monthly average)

    I have a feeling some 'countif' formula would work but I am not sure how to do this. Can you help?

    Thanks in advance!

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Sales daily sales data to be aggregated into monthly data (not using pivot table)

    Hi -

    I think SUMPRODUCT is actually the function to use. It can sum each entry in the same month. So you just write a SUMPRODUCT function for each month you want a total for.

    Hope this helps.

  3. #3
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Sales daily sales data to be aggregated into monthly data (not using pivot table)

    ="MARCH 2013 - $"&SUMIFS(B:B,A:A,">01/03/2013",A:A,"<31/03/2013")/COUNTIFS(A:A,">01/03/2013",A:A,"<31/03/2013")


    This assumes A:A is the date column and B:B is the $ Column. This adds all values for that month and then divides it by how many enteries there are in that month to give a average. If you just want the total for the month remove the "/COUNTIFS..." bit.

    EDIT - Change the dates to the same format that you use
    Last edited by Harribone; 03-27-2013 at 05:33 PM.
    Say thanks, click *

  4. #4
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Sales daily sales data to be aggregated into monthly data (not using pivot table)

    I have an AVERAGEIF function in Excel 2013... do you?

  5. #5
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Sales daily sales data to be aggregated into monthly data (not using pivot table)

    If column A is your dates and column b is your amounts, I would add C2 (or C1 if you do not have headers):

    =MONTH(A2)

    Fill this formula down enough rows to cover all of your date entries.

    The formula for your averages would be:

    =AVERAGEIF(C2:C1000,1,B2:B1000) I have used 1000 here for the example; you should actually use the row number of your last row of data. Also, where you see in the formula the number 1 between commas, you should input the month number you would like averaged. This formula looks at column C, the month numbers you put in, for all January entries (change the 1 for other months). When it finds one, it averages the value in column B for the same row. I hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1