Hi,

By the way the pivot table solution will automatically detect text date
verses real dates and group them differently, alerting you to a problem.

Also the pivot table automatically will run subtotals by year and month.
The month subtotals will automatically appear as the count. To add the year
subtotal select the year field in the pivot table and choose Pivot Table,
Subtotals.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"George Gee" wrote:

Hi Bernard

Thanks for your reply, I've got it sorted now, hundreds of entries
that are just a letter "a", I'm replacing these gradually with a date.

Thanks again
George Gee


"Bernard Liengme" wrote in message
...
Barry's formula is interesting. I would have used
=SUMPRODUCT(--(YEAR(A1:A100)=2000),--(MONTH(A1:A100)=1))
You can see how this is similar to
=SUMPRODUCT(--(YEAR(A1:A100)=2000))

Odd that one works and the other does not. I wonder if one or more cells
do not have real dates.
You say you have 4500 rows. What is the result from =COUNT(A1:A4500) ? If
every cell has a real date value, the result should be 4500.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"George Gee" wrote in message
...
Hi Barry

Thanks for your reply
The first part for the month works fine.
The second part for the year, I'm getting #VALUE!

Only 4500 rows

Thanks again
George Gee


"barry houdini" wrote in message
...
Hello George,

For January 2000 you could use a formula like

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000"))

for the whole year

=SUMPRODUCT(--(YEAR(A1:A100)=2000))

extend the range as necessary but you can't use the whole column
unless you have Excel 2007