countif within date ranges

N

naz

Im trying to count the number of entries for every month from a list of cells.
I've got the basic count if function but how do i tell it to count every
entry for jan 07?
Should i no be using countif?

Thanks
 
B

Bob Phillips

=SUMPRODUCT(--(YEAR(A2:A20)=2007),--(MONTH(A2:A20)=1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dave Peterson

=countif(a:a,">="&date(2007,1,1)) - countif(a:a,">"&date(2007,1,31))

Count the days larger than or equal to than Jan 1, 2007 and then subtract the
number of days larger than Jan 31, 2007.
 
N

naz

Thanks bob but i cant get it to work im getting the #value! error and i dont
understand why...heres what im working with...

Date requested
01/01/07
02/01/07
01/02/07
02/02/07
31/12/06

raised in jan07 =
raised in feb 07 =
 
B

Bob Phillips

Works fine for me naz. Are the cells formatted as text? Is there an error in
one of the cells?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
N

naz

yup it was the format..many thanks

Bob Phillips said:
Works fine for me naz. Are the cells formatted as text? Is there an error in
one of the cells?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
N

naz

thats great, thanks dave

Dave Peterson said:
=countif(a:a,">="&date(2007,1,1)) - countif(a:a,">"&date(2007,1,31))

Count the days larger than or equal to than Jan 1, 2007 and then subtract the
number of days larger than Jan 31, 2007.
Click to expand...
 
Top