Calculating mumber of incidents per month

MarkoBrit

New Member
Joined
Mar 6, 2019
Messages
12
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Try this with start date in C1 and end date in D1:

=COUNTIFS(B2:B1000,">="&C1,B2:B1000,"<"&D1+1)
 
Upvote 0
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

Farnborough03/01/2019Slip. Trip from HeightEmployeeMinorAberdeen0Incl HO49
Leeds06/01/2019Contact with Sharp ObjectEmployeeMinorArnotts0
Nottingham07/01/2019Hit by Something StationaryVisitorMinorBelfast0Minor45
Nottingham07/01/2019Hit by Something StationaryVisitorMinorBirmingham3Significant3
Gateshead09/01/2019Slip, fall same levelEmployeeMinorBlanchardstown0Major1
Factory Shop10/01/2019Ill HealthEmployeeMinorBristol1RIDDOR0
Thurrock17/01/2019Contact with Sharp ObjectEmployeeMinorCardiff1
Birmingham22/01/2019Slip. Trip from HeightVisitorMinorCroydon0JAN12
Bristol25/01/2019Slip, fall same levelEmployeeMinorDundrum0FEB0
Thurrock25/01/2019Hit by something Moving/FallingVisitorMinorEdinburgh0MAR
Stockton26/01/2019Hit by Something StationaryVisitorMinorFareham2APR
Preston27/01/2019OtherVisitorMinorFarnborough1MAY
Liverpool03/02/2019Slip. Trip from HeightVisitorMinorGateshead1JUN
Leamington Spa03/02/2019Manual HandlingEmployeeMinorGlasgow0JUL
Stratford04/02/2019Assault-PhysicalEmployeeMajorHull0AUG
Fareham04/02/2019Trapped between SomethingEmployeeMinorLeamington Spa2SEPT
Thurrock06/02/2019Manual HandlingEmployeeMinorLeeds2OCT
Lincoln19/02/2019Hit by Something StationaryVisitorMinorLincoln1NOV
Southampton19/02/2019OtherEmployeeMinorLiverpool2DEC

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Ok well if you think that works for you then it works for you. Looking at what you have its the wrong approach.
 
Upvote 0
If you will only have one years worth of data


Excel 2013/2016
ABCDEFGHIJK
1
2Farnborough03/01/2019Slip. Trip from HeightEmployeeMinorAberdeen0Incl HO49
3Leeds06/01/2019Contact with Sharp ObjectEmployeeMinorArnotts0
4Nottingham07/01/2019Hit by Something StationaryVisitorMinorBelfast0Minor45
5Nottingham07/01/2019Hit by Something StationaryVisitorMinorBirmingham3Significant3
6Gateshead09/01/2019Slip, fall same levelEmployeeMinorBlanchardstown0Major1
7Factory Shop10/01/2019Ill HealthEmployeeMinorBristol1RIDDOR0
8Thurrock17/01/2019Contact with Sharp ObjectEmployeeMinorCardiff1
9Birmingham22/01/2019Slip. Trip from HeightVisitorMinorCroydon0JAN12
10Bristol25/01/2019Slip, fall same levelEmployeeMinorDundrum0FEB7
11Thurrock25/01/2019Hit by something Moving/FallingVisitorMinorEdinburgh0MAR0
12Stockton26/01/2019Hit by Something StationaryVisitorMinorFareham2APR0
13Preston27/01/2019OtherVisitorMinorFarnborough1MAY0
14Liverpool03/02/2019Slip. Trip from HeightVisitorMinorGateshead1JUN0
15Leamington Spa03/02/2019Manual HandlingEmployeeMinorGlasgow0JUL0
16Stratford04/02/2019Assault-PhysicalEmployeeMajorHull0AUG0
17Fareham04/02/2019Trapped between SomethingEmployeeMinorLeamington Spa2SEPT0
18Thurrock06/02/2019Manual HandlingEmployeeMinorLeeds2OCT0
19Lincoln19/02/2019Hit by Something StationaryVisitorMinorLincoln1NOV0
20Southampton19/02/2019OtherEmployeeMinorLiverpool2DEC0
Sheet1
Cell Formulas
RangeFormula
K9=SUMPRODUCT(--(MONTH($B$2:$B$20)=ROWS($A$1:A1)))
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top