Count Incidents by date

B

bj6264

Board Regular
Joined
May 23, 2010
Messages
50
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.

[TABLE="width: 222"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Incident[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DA[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CB[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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!)
 
Upvote 0
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?
 
Upvote 0
Whats wrong with just copying and pasting the formula i provided into C2 and dragging down?

This is what i get:

[TABLE="width: 211"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Incident[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]23/01/2019[/TD]
[TD]BA[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]23/01/2019[/TD]
[TD]CD[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]23/01/2019[/TD]
[TD]CA[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]23/01/2019[/TD]
[TD]BA[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]23/01/2019[/TD]
[TD]DA[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]CD[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]BA[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]BA[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]AD[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]CD[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]BA[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]AB[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]24/01/2019[/TD]
[TD]CB[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Are you expecting something else?
 
Upvote 0
Or are you looking for something like


Excel 2013/2016
ABCDEF
1DateIncidentBACD
223/01/2019BA23/01/201921
323/01/2019CD24/01/201932
423/01/2019CA
523/01/2019BA
623/01/2019DA
724/01/2019CD
824/01/2019BA
924/01/2019BA
1024/01/2019AD
1124/01/2019CD
1224/01/2019BA
1324/01/2019AB
1424/01/2019CB
Sheet5
Cell Formulas
RangeFormula
E2=COUNTIFS($A:$A,$D2,$B:$B,E$1)
 
Upvote 0
bj6264 said:
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!)
Click to expand...

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.
 
Upvote 0
1ptcgi.gif
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Counting specific dates from multiple columns
Replies
2
Views
451
mattbird
M
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
344
alessiaexcels
A
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
1K
etaf
etaf
L
  • Question Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
1K
Armstrong_N14
A
C
  • Solved
Countifs Number Of Times Team Play on each weekday
Replies
3
Views
345
CordingBags
C

Share this page

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