How do I count cells in Excel between date values?

R

RJ1234

I want to count the number of records in a colum by looking at the date and
counting between date values for example count records (cells) between Jan 1
2003 and Dec 31 2004. I have used the CountIF function and can make it work
on >1/1/2003 for example but I cannot work out how to include < (i.e. between
dates)
 
R

Ron Rosenfeld

I want to count the number of records in a colum by looking at the date and
counting between date values for example count records (cells) between Jan 1
2003 and Dec 31 2004. I have used the CountIF function and can make it work
on >1/1/2003 for example but I cannot work out how to include < (i.e. between
dates)
Click to expand...

=COUNTIF(A:A,">=1/1/2003")-COUNTIF(A:A,">12/31/2004")

or, if not in the US, better might be:

=COUNTIF(A:A,">="&DATE(2003,1,1))-COUNTIF(A:A,">"&DATE(2004,12,31))


--ron
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A100>=--("2004/01/01")),--(A1:A100<=--("2004/12/31")))

or

=SUMPRODUCT(--(A1:A100>=B1),--(A1:A100<=C1))

if you store the dates in B1 and C1
 
J

Jason Morin

Using COUNTIF for this can be a pain. How about
SUMPRODUCT?

=SUMPRODUCT(--(A1:A100>="1/1/03"+0),--
(A1:A100<="12/31/04"+0))

HTH
Jason
Atlanta, GA
 
Top