Calculating mumber of incidents per month

[MarkoBrit]

Hi , In Column B I have dates of incidents . How do I calculate how many incidents were in each month?
I tried this =COUNTIF((B2:B1000),"<31/01/2019") Which obviously calculates a range but is there a formula I can use that would just calculate each months incidents?

 

[sandy666]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td=bgcolor:#5B9BD5]Column6[/td][td=bgcolor:#5B9BD5]Column7[/td][td=bgcolor:#5B9BD5]Column8[/td][td=bgcolor:#5B9BD5]Column9[/td][td=bgcolor:#5B9BD5]Column10[/td][td=bgcolor:#5B9BD5]Column11[/td][td][/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Farnborough[/td][td=bgcolor:#DDEBF7]

03/01/2019​

[/td][td=bgcolor:#DDEBF7]Slip. Trip from Height[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Aberdeen[/td][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Incl HO[/td][td=bgcolor:#DDEBF7]

49​

[/td][td][/td][td=bgcolor:#E2EFDA]January[/td][td=bgcolor:#E2EFDA]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Leeds[/td][td]

06/01/2019​

[/td][td]Contact with Sharp Object[/td][td]Employee[/td][td]Minor[/td][td][/td][td]Arnotts[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][td]February[/td][td]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Nottingham[/td][td=bgcolor:#DDEBF7]

07/01/2019​

[/td][td=bgcolor:#DDEBF7]Hit by Something Stationary[/td][td=bgcolor:#DDEBF7]Visitor[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Belfast[/td][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7]

45​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Nottingham[/td][td]

07/01/2019​

[/td][td]Hit by Something Stationary[/td][td]Visitor[/td][td]Minor[/td][td][/td][td]Birmingham[/td][td]

3​

[/td][td][/td][td]Significant[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Gateshead[/td][td=bgcolor:#DDEBF7]

09/01/2019​

[/td][td=bgcolor:#DDEBF7]Slip, fall same level[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Blanchardstown[/td][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Major[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Factory Shop[/td][td]

10/01/2019​

[/td][td]Ill Health[/td][td]Employee[/td][td]Minor[/td][td][/td][td]Bristol[/td][td]

1​

[/td][td][/td][td]RIDDOR[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Thurrock[/td][td=bgcolor:#DDEBF7]

17/01/2019​

[/td][td=bgcolor:#DDEBF7]Contact with Sharp Object[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Cardiff[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Birmingham[/td][td]

22/01/2019​

[/td][td]Slip. Trip from Height[/td][td]Visitor[/td][td]Minor[/td][td][/td][td]Croydon[/td][td]

0​

[/td][td][/td][td]JAN[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Bristol[/td][td=bgcolor:#DDEBF7]

25/01/2019​

[/td][td=bgcolor:#DDEBF7]Slip, fall same level[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Dundrum[/td][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FEB[/td][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thurrock[/td][td]

25/01/2019​

[/td][td]Hit by something Moving/Falling[/td][td]Visitor[/td][td]Minor[/td][td][/td][td]Edinburgh[/td][td]

0​

[/td][td][/td][td]MAR[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Stockton[/td][td=bgcolor:#DDEBF7]

26/01/2019​

[/td][td=bgcolor:#DDEBF7]Hit by Something Stationary[/td][td=bgcolor:#DDEBF7]Visitor[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Fareham[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]APR[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Preston[/td][td]

27/01/2019​

[/td][td]Other[/td][td]Visitor[/td][td]Minor[/td][td][/td][td]Farnborough[/td][td]

1​

[/td][td][/td][td]MAY[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Liverpool[/td][td=bgcolor:#DDEBF7]

03/02/2019​

[/td][td=bgcolor:#DDEBF7]Slip. Trip from Height[/td][td=bgcolor:#DDEBF7]Visitor[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Gateshead[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]JUN[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Leamington Spa[/td][td]

03/02/2019​

[/td][td]Manual Handling[/td][td]Employee[/td][td]Minor[/td][td][/td][td]Glasgow[/td][td]

0​

[/td][td][/td][td]JUL[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Stratford[/td][td=bgcolor:#DDEBF7]

04/02/2019​

[/td][td=bgcolor:#DDEBF7]Assault-Physical[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Major[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Hull[/td][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]AUG[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Fareham[/td][td]

04/02/2019​

[/td][td]Trapped between Something[/td][td]Employee[/td][td]Minor[/td][td][/td][td]Leamington Spa[/td][td]

2​

[/td][td][/td][td]SEPT[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Thurrock[/td][td=bgcolor:#DDEBF7]

06/02/2019​

[/td][td=bgcolor:#DDEBF7]Manual Handling[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Leeds[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]OCT[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lincoln[/td][td]

19/02/2019​

[/td][td]Hit by Something Stationary[/td][td]Visitor[/td][td]Minor[/td][td][/td][td]Lincoln[/td][td]

1​

[/td][td][/td][td]NOV[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Southampton[/td][td=bgcolor:#DDEBF7]

19/02/2019​

[/td][td=bgcolor:#DDEBF7]Other[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7]Minor[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Liverpool[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DEC[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractMonth = Table.TransformColumns(Source, {{"Column2", each Date.MonthName(_), type text}}),
    Group = Table.Group(ExtractMonth, {"Column2"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    Group[/SIZE]

 

[MarkoBrit]

Thanks Sandy666, but I have no idea what you did there , where do I put that code and do I need to chnge any of it and where did you put that "column 2" and "count" ? on the same sheet?

 

[sandy666]

this is PowerQuery (Get&Transform) for Excel 2010 and higher

you need create table (Ctrl+T) as you see Blue table, then load it to PQ Editor and adapt M-code to your needs and next load result (Green) to the sheet

What is Power Query

Getting Started with Get & Transform in Excel

Microsoft Power Query for Excel Help