How to count dates by month

Question

item 1	item 2	item 3
Jan-18	Jan-18	Jan-18
Jun-17	Apr-17	Apr-17
Jan-17	Oct-16	Oct-16
Jan-16	Jan-16	Jan-16
Jun-15	Jul-15	Jul-15
Jan-15	Jan-15	Jul-15
Jun-14	Apr-14	Apr-14
Jan-14	Jan-14	Jan-14
Jul-13	Oct-13	Oct-13
Jan-13	May-13	May-13
Jul-12	Jul-12	Jul-12
Jan-12	Oct-11	Oct-11
Jan-11	Dec-10	Dec-10
Jul-10
Jan-10
Jan-09
May-08
Jan-08
Jan-07
Aug-05

I have a list of dates for particular items. I would like to create a table that will count the number of occurrences during each month. 

For example: 

	item 1	item 2	item 3
jan	11	4	4
feb	0	0	0
mar	0	0	0
apr	0	2	2
may	1	1	1
jun	3	0	0
jul	3	2	2
aug	1	0	0
sep	0	0	0
oct	0	3	3
nov	0	0	0
dec	0	1	1

From there I should be able to create a pivot table to see trends per month.

Some problems I had with "=DCOUNT" the data would not respond to month alone because it is a date.

Thanks!

Answer 1

Enter below formula in cell F2 & copy down & to the right:

=SUMPRODUCT((TEXT($A$2:$C$21,"mmm")=$E2)*($A$2:$C$21<>"")*($A$1:$C$1=F$1))

Using formulas above - you seem to have mentioned to create a table like above and from there create your pivot table.

Regards,

Amit Tandon

www.globaliconnect.com

Answer 2

Hi,

I have done this with the help of Data > Get & Transform.  Here is the code which you have to paste in the Query Editor

let

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"item 1", type datetime}, {"item 2", type datetime}, {"item 3", type datetime}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "item 1", "item 2", "item 3"}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),

    #"Inserted Month Name" = Table.AddColumn(#"Removed Columns", "Month Name", each Date.MonthName([Value]), type text),

    #"Pivoted Column" = Table.Pivot(#"Inserted Month Name", List.Distinct(#"Inserted Month Name"[Attribute]), "Attribute", "Value", List.Count),

    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Month Name", Order.Ascending}})

in

    #"Sorted Rows"

The result looks like this

My solution only shows months for which there is data.  Can you live with this?  You may download the solution workbook from here.