Count Incidents by date

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.

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>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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:

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?
 
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
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.
 
Upvote 0
1ptcgi.gif
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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