SUM daily sale data to be monthly data

[ibrahimdaas]

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?

 

[Kamolga]

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

 

[ibrahimdaas]

I know how to do it by pivot table ... but I don't want it with the use of the pivot table..

Now I found this video on youtube :

https://www.youtube.com/watch?v=6p6wzUx6H1c

but it was only for a one year .. I want the same for multiple years not only one year

 

[Kamolga]

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

=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