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

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column2

Count

Farnborough

03/01/2019​

Slip. Trip from Height

Employee

Minor

Aberdeen

0​

Incl HO

49​

January

12​

Leeds

06/01/2019​

Contact with Sharp Object

Employee

Minor

Arnotts

0​

February

7​

Nottingham

07/01/2019​

Hit by Something Stationary

Visitor

Minor

Belfast

0​

Minor

45​

Nottingham

07/01/2019​

Hit by Something Stationary

Visitor

Minor

Birmingham

3​

Significant

3​

Gateshead

09/01/2019​

Slip, fall same level

Employee

Minor

Blanchardstown

0​

Major

1​

Factory Shop

10/01/2019​

Ill Health

Employee

Minor

Bristol

1​

RIDDOR

0​

Thurrock

17/01/2019​

Contact with Sharp Object

Employee

Minor

Cardiff

1​

Birmingham

22/01/2019​

Slip. Trip from Height

Visitor

Minor

Croydon

0​

JAN

12​

Bristol

25/01/2019​

Slip, fall same level

Employee

Minor

Dundrum

0​

FEB

0​

Thurrock

25/01/2019​

Hit by something Moving/Falling

Visitor

Minor

Edinburgh

0​

MAR

Stockton

26/01/2019​

Hit by Something Stationary

Visitor

Minor

Fareham

2​

APR

Preston

27/01/2019​

Other

Visitor

Minor

Farnborough

1​

MAY

Liverpool

03/02/2019​

Slip. Trip from Height

Visitor

Minor

Gateshead

1​

JUN

Leamington Spa

03/02/2019​

Manual Handling

Employee

Minor

Glasgow

0​

JUL

Stratford

04/02/2019​

Assault-Physical

Employee

Major

Hull

0​

AUG

Fareham

04/02/2019​

Trapped between Something

Employee

Minor

Leamington Spa

2​

SEPT

Thurrock

06/02/2019​

Manual Handling

Employee

Minor

Leeds

2​

OCT

Lincoln

19/02/2019​

Hit by Something Stationary

Visitor

Minor

Lincoln

1​

NOV

Southampton

19/02/2019​

Other

Employee

Minor

Liverpool

2​

DEC

[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