Calculating mumber of incidents per month

MarkoBrit

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

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,079
Office Version
365
Platform
Windows
Try this with start date in C1 and end date in D1:

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

MarkoBrit

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,079
Office Version
365
Platform
Windows
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.
 

MarkoBrit

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,079
Office Version
365
Platform
Windows
Ok well if you think that works for you then it works for you. Looking at what you have its the wrong approach.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
If you will only have one years worth of data

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Farnborough</td><td style="text-align: right;;">03/01/2019</td><td style=";">Slip. Trip from Height</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Aberdeen</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Incl HO</td><td style="text-align: right;;">49</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Leeds</td><td style="text-align: right;;">06/01/2019</td><td style=";">Contact with Sharp Object</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Arnotts</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Nottingham</td><td style="text-align: right;;">07/01/2019</td><td style=";">Hit by Something Stationary</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Belfast</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Minor</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Nottingham</td><td style="text-align: right;;">07/01/2019</td><td style=";">Hit by Something Stationary</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Birmingham</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">Significant</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Gateshead</td><td style="text-align: right;;">09/01/2019</td><td style=";">Slip, fall same level</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Blanchardstown</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Major</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Factory Shop</td><td style="text-align: right;;">10/01/2019</td><td style=";">Ill Health</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Bristol</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">RIDDOR</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Thurrock</td><td style="text-align: right;;">17/01/2019</td><td style=";">Contact with Sharp Object</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Cardiff</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Birmingham</td><td style="text-align: right;;">22/01/2019</td><td style=";">Slip. Trip from Height</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Croydon</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">JAN</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Bristol</td><td style="text-align: right;;">25/01/2019</td><td style=";">Slip, fall same level</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Dundrum</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">FEB</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Thurrock</td><td style="text-align: right;;">25/01/2019</td><td style=";">Hit by something Moving/Falling</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Edinburgh</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">MAR</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Stockton</td><td style="text-align: right;;">26/01/2019</td><td style=";">Hit by Something Stationary</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Fareham</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">APR</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Preston</td><td style="text-align: right;;">27/01/2019</td><td style=";">Other</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Farnborough</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">MAY</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Liverpool</td><td style="text-align: right;;">03/02/2019</td><td style=";">Slip. Trip from Height</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Gateshead</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">JUN</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Leamington Spa</td><td style="text-align: right;;">03/02/2019</td><td style=";">Manual Handling</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Glasgow</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">JUL</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Stratford</td><td style="text-align: right;;">04/02/2019</td><td style=";">Assault-Physical</td><td style=";">Employee</td><td style=";">Major</td><td style="text-align: right;;"></td><td style=";">Hull</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">AUG</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Fareham</td><td style="text-align: right;;">04/02/2019</td><td style=";">Trapped between Something</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Leamington Spa</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">SEPT</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Thurrock</td><td style="text-align: right;;">06/02/2019</td><td style=";">Manual Handling</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Leeds</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">OCT</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Lincoln</td><td style="text-align: right;;">19/02/2019</td><td style=";">Hit by Something Stationary</td><td style=";">Visitor</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Lincoln</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">NOV</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Southampton</td><td style="text-align: right;;">19/02/2019</td><td style=";">Other</td><td style=";">Employee</td><td style=";">Minor</td><td style="text-align: right;;"></td><td style=";">Liverpool</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">DEC</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">$B$2:$B$20</font>)=ROWS(<font color="Green">$A$1:A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,472
Messages
5,468,823
Members
406,612
Latest member
pedad

This Week's Hot Topics

Top