I have a column with lots of dates in.

I want a formula to count the number of dates, ie jan 2006 = 12

Any ideas,
Ive tried CountIf, and 2006 but i think the date formatting is messing
with it.


--
Mark

http://www.mozilla.com/firefox/ - Firefox Rules!
http://fireftp.mozdev.org/ - FTP Plugin for Firefox
http://ietab.mozdev.org/ - IE tab. Get Windows updates in Firefox

Try this:

=SUMPRODUCT(--(TEXT(A1:A100,"mmm yyyy")="Jan 2006"))

--
Biff
Microsoft Excel MVP


"Mark Solesbury" <marksolesbury@asearchenginethatnowdoeseverythingm ail.com
wrote in message ...
I have a column with lots of dates in.

I want a formula to count the number of dates, ie jan 2006 = 12

Any ideas,
Ive tried CountIf, and 2006 but i think the date formatting is messing
with it.


--
Mark

http://www.mozilla.com/firefox/ - Firefox Rules!
http://fireftp.mozdev.org/ - FTP Plugin for Firefox
http://ietab.mozdev.org/ - IE tab. Get Windows updates in Firefox

Try:

=SUMPRODUCT((MONTH(A1:A100)=1)*(YEAR(A1:A100)=2008 ))

Change the month number, year and range to suit.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mark Solesbury" <marksolesbury@asearchenginethatnowdoeseverythingm ail.com
wrote in message ...
I have a column with lots of dates in.

I want a formula to count the number of dates, ie jan 2006 = 12

Any ideas,
Ive tried CountIf, and 2006 but i think the date formatting is messing
with it.


--
Mark

http://www.mozilla.com/firefox/ - Firefox Rules!
http://fireftp.mozdev.org/ - FTP Plugin for Firefox
http://ietab.mozdev.org/ - IE tab. Get Windows updates in Firefox

Hi Mark.

Here are couple of alternatives:

=SUMPRODUCT((MONTH(A2:A37)=5)*(YEAR(A2:A37)=2008))

=SUM((A2:A37=D3)*(A2:A37<=E3))
here you enter the first and last day of a month in the cells D3 and E3.
This formula is an array formula so you press Shift+Ctrl+Enter to enter it.

=SUMPRODUCT(--((TEXT(A2:A37,"M YY")="5 08")))
or its equivalent array version
=SUM(N((TEXT(A2:A37,"M YY")="5 08")))

All of these approaches assume the date in the range are entered as dates
not text.

Cheers,
Shane Devenshire
Microsoft Excel MVP



"Mark Solesbury" <marksolesbury@asearchenginethatnowdoeseverythingm ail.com
wrote in message ...
I have a column with lots of dates in.

I want a formula to count the number of dates, ie jan 2006 = 12

Any ideas,
Ive tried CountIf, and 2006 but i think the date formatting is messing
with it.


--
Mark

http://www.mozilla.com/firefox/ - Firefox Rules!
http://fireftp.mozdev.org/ - FTP Plugin for Firefox
http://ietab.mozdev.org/ - IE tab. Get Windows updates in Firefox