count number of times a date is shown in a column

I have a list of dates in a column in Excel and would like to show a total for each unique date.

So say this is the original list:

03/05/2019
03/05/2019
03/05/2019
03/05/2019
03/05/2019
07/05/2019
07/05/2019
07/05/2019
08/05/2019
08/05/2019
08/05/2019
09/05/2019
09/05/2019

I'd like to convert that to this:

03/05/2019,5
07/05/2019,3
08/05/2019,3
09/05/2019,2

Thanks for your help!

3 Answers

You first need to get a column consisting of unique values. This can be done following this:

or if you want to manually use equations,

Then, to get the actual counts of those unique values, you can use =COUNTIF(). So say your original list is in A1:A13 and the unique values are in B1:B4. You would do =COUNTIF($A$1:$A$13,B1) in B1, and copy/paste that to B2, B3 and B4.

If your goal is to actually have each unique value with it's count in one cell seperated with a comma you could try this rather long formula:

Formula in C1:

=TEXT(INDEX($A$1:$A$13,SMALL(INDEX((MATCH($A$1:$A$13&"",$A$1:$A$13&"",0)<>ROW($A$1:$A$13))*10^10+ROW($A$1:$A$13),0),ROW(1:1))),"dd/mm/yyyy")&","&COUNTIF($A$1:$A$13,INDEX($A$1:$A$13,SMALL(INDEX((MATCH($A$1:$A$13&"",$A$1:$A$13&"",0)<>ROW($A$1:$A$13))*10^10+ROW($A$1:$A$13),0),ROW(1:1))))

Drag down...

Hindsight, your requirement was different but i'll leave the other option there for future reference. With headers and a column for unique values and another one for the appropriate count see example below:

Formula in C2:

=INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$14),0),0))

Formula in D2:

=COUNTIF($A$2:$A$14,C2)

Drag both down...

You can do this with formulas, and would probably want that if your data is dynamic and you want the result to be automatically updated. Otherwise, I agree with Scott Craner's comment about using a pivot table. You can get your result with a few clicks of the mouse.

You'll want a column heading. You just select the data and then insert a pivot table from the menu or toolbar. Drag the column heading to the Rows window and to the Values window (and verify that the aggregation method selected is Count). There are a couple of administrative selections, like where to stick the pivot table, and whether to include a grand total.

And you're done.