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?

 

[steve the fish]

Try this with start date in C1 and end date in D1:

=COUNTIFS(B2:B1000,">="&C1,B2:B1000,"<"&D1+1)

 

[MarkoBrit]

Try this with start date in C1 and end date in D1:

=COUNTIFS(B2:B1000,">="&C1,B2:B1000,"<"&D1+1)

That won't work for this sheet as below

[TABLE="width: 1065"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Farnborough[/TD]
[TD]03/01/2019[/TD]
[TD]Slip. Trip from Height[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Aberdeen[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Incl HO[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[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="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nottingham[/TD]
[TD]07/01/2019[/TD]
[TD]Hit by Something Stationary[/TD]
[TD]Visitor[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Belfast[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Minor[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[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="align: right"]3[/TD]
[TD][/TD]
[TD]Significant[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Gateshead[/TD]
[TD]09/01/2019[/TD]
[TD]Slip, fall same level[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Blanchardstown[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Major[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[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="align: right"]1[/TD]
[TD][/TD]
[TD]RIDDOR[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Thurrock[/TD]
[TD]17/01/2019[/TD]
[TD]Contact with Sharp Object[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Cardiff[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="align: right"]0[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Bristol[/TD]
[TD]25/01/2019[/TD]
[TD]Slip, fall same level[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Dundrum[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]FEB[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[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="align: right"]0[/TD]
[TD][/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stockton[/TD]
[TD]26/01/2019[/TD]
[TD]Hit by Something Stationary[/TD]
[TD]Visitor[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Fareham[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]APR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Preston[/TD]
[TD]27/01/2019[/TD]
[TD]Other[/TD]
[TD]Visitor[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Farnborough[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]MAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]03/02/2019[/TD]
[TD]Slip. Trip from Height[/TD]
[TD]Visitor[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Gateshead[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]JUN[/TD]
[TD][/TD]
[/TR]
[TR]
[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="align: right"]0[/TD]
[TD][/TD]
[TD]JUL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stratford[/TD]
[TD]04/02/2019[/TD]
[TD]Assault-Physical[/TD]
[TD]Employee[/TD]
[TD]Major[/TD]
[TD][/TD]
[TD]Hull[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[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="align: right"]2[/TD]
[TD][/TD]
[TD]SEPT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thurrock[/TD]
[TD]06/02/2019[/TD]
[TD]Manual Handling[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Leeds[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]OCT[/TD]
[TD][/TD]
[/TR]
[TR]
[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="align: right"]1[/TD]
[TD][/TD]
[TD]NOV[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southampton[/TD]
[TD]19/02/2019[/TD]
[TD]Other[/TD]
[TD]Employee[/TD]
[TD]Minor[/TD]
[TD][/TD]
[TD]Liverpool[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]DEC[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Well you dont have to use C1 and D1. You can use a different cell. You could hardcode it into the formula. You could even use a pivot table instead.

 

[sykes]

Here's another approach: https://exceljet.net/formula/count-birthdays-by-month

 

[steve the fish]

Here's another approach: https://exceljet.net/formula/count-birthdays-by-month

The wrong approach in this instance as that isnt concerned with year. This should be.

 

[MarkoBrit]

The wrong approach in this instance as that isnt concerned with year. This should be.

I just want a formula that counts incidents in Jan , Feb etc , without having a start date or an end date as those are irrelevant.

 

[sunrise06]

Use a PivotTable and group by month.

 

[steve the fish]

Ok well if you think that works for you then it works for you. Looking at what you have its the wrong approach.

 

[Fluff]

If you will only have one years worth of data

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1
2	Farnborough	03/01/2019	Slip. Trip from Height	Employee	Minor		Aberdeen	0		Incl HO	49
3	Leeds	06/01/2019	Contact with Sharp Object	Employee	Minor		Arnotts	0
4	Nottingham	07/01/2019	Hit by Something Stationary	Visitor	Minor		Belfast	0		Minor	45
5	Nottingham	07/01/2019	Hit by Something Stationary	Visitor	Minor		Birmingham	3		Significant	3
6	Gateshead	09/01/2019	Slip, fall same level	Employee	Minor		Blanchardstown	0		Major	1
7	Factory Shop	10/01/2019	Ill Health	Employee	Minor		Bristol	1		RIDDOR	0
8	Thurrock	17/01/2019	Contact with Sharp Object	Employee	Minor		Cardiff	1
9	Birmingham	22/01/2019	Slip. Trip from Height	Visitor	Minor		Croydon	0		JAN	12
10	Bristol	25/01/2019	Slip, fall same level	Employee	Minor		Dundrum	0		FEB	7
11	Thurrock	25/01/2019	Hit by something Moving/Falling	Visitor	Minor		Edinburgh	0		MAR	0
12	Stockton	26/01/2019	Hit by Something Stationary	Visitor	Minor		Fareham	2		APR	0
13	Preston	27/01/2019	Other	Visitor	Minor		Farnborough	1		MAY	0
14	Liverpool	03/02/2019	Slip. Trip from Height	Visitor	Minor		Gateshead	1		JUN	0
15	Leamington Spa	03/02/2019	Manual Handling	Employee	Minor		Glasgow	0		JUL	0
16	Stratford	04/02/2019	Assault-Physical	Employee	Major		Hull	0		AUG	0
17	Fareham	04/02/2019	Trapped between Something	Employee	Minor		Leamington Spa	2		SEPT	0
18	Thurrock	06/02/2019	Manual Handling	Employee	Minor		Leeds	2		OCT	0
19	Lincoln	19/02/2019	Hit by Something Stationary	Visitor	Minor		Lincoln	1		NOV	0
20	Southampton	19/02/2019	Other	Employee	Minor		Liverpool	2		DEC	0
Sheet1

Cell Formulas
Range	Formula
K9	=SUMPRODUCT(--(MONTH($B$2:$B$20)=ROWS($A$1:A1)))