I'm using Excel 2003, Win XP. I just want to enter a funtion/formula that
counts how many times a record appears in a range between two specific dates
rather than having to filter the records and do a count. I've been trying
the COUNTIF function but without any luck. Example:
=COUNTIF(A1:A200,=12012008,<=12312008). I know this is completely wrong
but you get the idea. Thanks in advance. Jeremy.

Try one of these:

=COUNTIF(A1:A200,"="&DATE(2008,12,1))-COUNTIF(A1:A200,""&DATE(2008,12,31))

Or, better to use cells to hold the date boundaries:

C1 = lower boundary = 12/1/2008
D1 = upper boundary = 12/31/2008

=COUNTIF(A1:A200,"="&C1)-COUNTIF(A1:A200,""&D1)

--
Biff
Microsoft Excel MVP


"Jeremy Prosser" wrote in message
...
I'm using Excel 2003, Win XP. I just want to enter a funtion/formula that
counts how many times a record appears in a range between two specific
dates
rather than having to filter the records and do a count. I've been trying
the COUNTIF function but without any luck. Example:
=COUNTIF(A1:A200,=12012008,<=12312008). I know this is completely wrong
but you get the idea. Thanks in advance. Jeremy.

Try the SUMPRODUCT function:

Something like:

=SUMPRODUCT(--(A1:A200=DATE(2008,12,1)),--(A1:A200<=DATE(2008,12,31)))

You could also place your min and max dates in separate cells, such as B1
and B2. Then use:

=SUMPRODUCT(--(A1:A200=B1),--(A1:A200<=B2))

HTH
Elkar


"Jeremy Prosser" wrote:

I'm using Excel 2003, Win XP. I just want to enter a funtion/formula that
counts how many times a record appears in a range between two specific dates
rather than having to filter the records and do a count. I've been trying
the COUNTIF function but without any luck. Example:
=COUNTIF(A1:A200,=12012008,<=12312008). I know this is completely wrong
but you get the idea. Thanks in advance. Jeremy.

You need COUNTIF's big brother, SUMPRODUCT():

=SUMPRODUCT(--(A1:A100=DATEVALUE("12/1/2008")),--(A1:A100<=DATEVALUE("12/31/2008")))



--
Gary''s Student - gsnu200828


"Jeremy Prosser" wrote:

I'm using Excel 2003, Win XP. I just want to enter a funtion/formula that
counts how many times a record appears in a range between two specific dates
rather than having to filter the records and do a count. I've been trying
the COUNTIF function but without any luck. Example:
=COUNTIF(A1:A200,=12012008,<=12312008). I know this is completely wrong
but you get the idea. Thanks in advance. Jeremy.

And if the dates range is for a certain month/year, ie the range doesn't cut
across months, you could use this to count it for all dates falling in Dec
2008 (for eg):
=SUMPRODUCT(--(TEXT(A1:A200,"mmmyyyy")="Dec2008"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Jeremy Prosser" wrote:
I'm using Excel 2003, Win XP. I just want to enter a funtion/formula that
counts how many times a record appears in a range between two specific dates
rather than having to filter the records and do a count. I've been trying
the COUNTIF function but without any luck. Example:
=COUNTIF(A1:A200,=12012008,<=12312008). I know this is completely wrong
but you get the idea. Thanks in advance. Jeremy.