SUM daily sale data to be monthly data

ibrahimdaas

New Member
Joined
Sep 1, 2014
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,


I have searched the forums for an answer to this but to no avail.


I am hoping someone may be able to help me,


I have a large data of Sales amounts sorted on a daily dates....these dates look like: 12/11/2017 then 30/4/2015 etc ...


I want to group the sales by month then by year, Say ... total sale number for the month of Nov 2015 is ... and the total sale of March 2014 is .....


How can I do that in excel?
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi,

By using a pivot table: select your data/insert tab/pivot table, put the date in row part (it will be in months but you can change it) and sum of sales in values
 
Last edited:
Upvote 0
Let's say you have the monthly dates Jan 12 Feb 12 March 12.....Jan 13 Feb 13 ... in row 1. So A1 is Jan 12 and you want the total sales for Jan 12 in B1.

Your data for dates are from A3 toA99 and sales from B3 to B99

In B2 I would enter
Code:
=sumifs(B3:B99,A3:A99,"[COLOR=#222222][FONT=Verdana]<="&EOMONTH(A1,0),[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]A3:A99[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana],">="&A1)[/FONT][/COLOR]
.

A1 is Jan 12 (so 1/1/2012 for excel). This formula will sum the daily sales (B3:B99) if dates (A3:A99) is inferior or equal to 31/01/2012 (EOMONTH(A1,0) and superior or equal to 1/1/2012 (A1). It would work for every year
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top