count my dates...

[canonelan2]

Column A has a long list of dates in it like this....

9/20/08
9/21/08
9/21/08
9/21/08
9/22/08
9/22/08
9/23/08
9/23/08
9/23/08
9/23/08

I need a formula to count the dates that are the same and display the count number.

result...

9/20/08 1
9/21/08 3
9/22/08 2
9/23/08 4
etc.

Any ideas?

[NBVC]

First create the column of unique dates using DATA|Filter|Advanced Filter...

Then use Countif() formula to count numbers...

e.g. =Countif($A$1:$A$100,B1) where A1:A100 is your original list and B1 is the first unique date...
copy formula down.

[canonelan2]

First create the column of unique dates using DATA|Filter|Advanced Filter...

Then use Countif() formula to count numbers...

e.g. =Countif($A$1:$A$100,B1) where A1:A100 is your original list and B1 is the first unique date...
copy formula down.

Countif is perfect! Thanks!

Question about the DATA|Filter|Advanced Filter, can something like that be done on the fly? (automatically?) So when I add new data to the main worksheet, it will notice I added dates and place the new dates on the secondary worksheet?

Thanks again!

[NBVC]

You'll need a macro for that... which you would might as well put the countif formula in too.

... but I am not a VBA programmer ...sorry.

[NBVC]

You'll need a macro for that... which you would might as well put the countif formula in too.

... but I am not a VBA programmer ...sorry.

[canonelan2]

that's fine... I just did a fill down to add the dates for the rest of the year. On days with no new data it will return a zero. That's fine by me.

Another question. Now that I have a list of entries per date (thanks to you)...

9/20/2008 14
9/21/2008 77
9/22/2008 119
9/23/2008 92
9/24/2008 10
9/25/2008 0

how can I make another list that will count by month?

August 1,957
September 312
October 0

Thanks!

[NBVC]

You would need to list the Months first in say E1:E12.. then use formula:

=SUMPRODUCT(--(TEXT($A$1:$A$6,"mmmm")=E1),$B$1:$B$6)

copied down. Adjust ranges to suit... where A1:A6 lists dates and B1:B6 corresponding numbers.