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

1. ## 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?

2. ## 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. ## 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

4. ## 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. ## 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!

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

#### 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