Count Incidents by date

bj6264

New Member
Joined
May 23, 2010
Messages
49
Hi, I have a spreadsheets that contains a list of incidents by date. The date therefore appears numerous times (column A) and various codes appear in the 2nd column (column B). How do I count the number of times the code in column B appears by date (column a)? Sounds simple but I have a brain fog at the moment! thanks.

Date Incident
23/01/2019 BA
23/01/2019 CD
23/01/2019 CA
23/01/2019 BA
23/01/2019 DA
24/01/2019 CD
24/01/2019 BA
24/01/2019 BA
24/01/2019 AD
24/01/2019 CD
24/01/2019 BA
24/01/2019 AB
24/01/2019 CB

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

bj6264

New Member
Joined
May 23, 2010
Messages
49
Thanks for this but I tried this and my date column kept appearing in the pivot table as year and quarters not the actual date. This was the reason I was then trying to use the countifs. (the file is from sharepoint which doesn't help!)
 

bj6264

New Member
Joined
May 23, 2010
Messages
49
Yes, was trying with the multiple criteria but kept getting "two few arguments". My formula didn't work. I used an ampersand to split the two arguments which was probably wrong. any suggestions?
 

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
264
Office Version
365
Platform
Windows
Whats wrong with just copying and pasting the formula i provided into C2 and dragging down?

This is what i get:

DateIncidentResults
23/01/2019BA2
23/01/2019CD1
23/01/2019CA1
23/01/2019BA2
23/01/2019DA1
24/01/2019CD2
24/01/2019BA3
24/01/2019BA3
24/01/2019AD1
24/01/2019CD2
24/01/2019BA3
24/01/2019AB1
24/01/2019CB1

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Are you expecting something else?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
Or are you looking for something like

<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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Incident</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">BA</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">23/01/2019</td><td style=";">BA</td><td style="text-align: right;;"></td><td style="text-align: right;;">23/01/2019</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">23/01/2019</td><td style=";">CD</td><td style="text-align: right;;"></td><td style="text-align: right;;">24/01/2019</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">23/01/2019</td><td style=";">CA</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;">5</td><td style="text-align: right;;">23/01/2019</td><td style=";">BA</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;">6</td><td style="text-align: right;;">23/01/2019</td><td style=";">DA</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;">7</td><td style="text-align: right;;">24/01/2019</td><td style=";">CD</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;">8</td><td style="text-align: right;;">24/01/2019</td><td style=";">BA</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;">9</td><td style="text-align: right;;">24/01/2019</td><td style=";">BA</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;">10</td><td style="text-align: right;;">24/01/2019</td><td style=";">AD</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;">11</td><td style="text-align: right;;">24/01/2019</td><td style=";">CD</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;">12</td><td style="text-align: right;;">24/01/2019</td><td style=";">BA</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;">13</td><td style="text-align: right;;">24/01/2019</td><td style=";">AB</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;">14</td><td style="text-align: right;;">24/01/2019</td><td style=";">CB</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></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)">Sheet5</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)">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A:$A,$D2,$B:$B,E$1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
78
Thanks for this but I tried this and my date column kept appearing in the pivot table as year and quarters not the actual date. This was the reason I was then trying to use the countifs. (the file is from sharepoint which doesn't help!)
Once the table is created, right click on the date ( or year whatever is showing on the date field), Select Group , you have option here to select months , days , years , Qtrs.
 

Forum statistics

Threads
1,085,311
Messages
5,382,901
Members
401,808
Latest member
huyennhiteen9xx

Some videos you may like

This Week's Hot Topics

Top