Countif Date Problem

[Guest]

How to make a countif function for the following case??

Cell A1 :01/09/2003
Cell A2 :30/09/2003
I want to count the number of date entry in column C which is within the
date range A1 >= column C >= A2

=countif(C:C, ">="&A1 & "<="&A2)
Would anyone know why the above equation is not work, many thanks.

 

[Guest]

This should work:
=SUMPRODUCT(--(C1:C65535>=A2),--(C1:C65535<=A3))

SUMPRODUCT can't do entire Columns, so you must use cell references.
One of the reasons your formula isn't working (at least as it's typed in
your post) is that it contains spaces. COUNTIF also typically uses only one
criteria.

tj

 

[Ron Rosenfeld]

How to make a countif function for the following case??

Cell A1 :01/09/2003
Cell A2 :30/09/2003
I want to count the number of date entry in column C which is within the
date range A1 >= column C >= A2

=countif(C:C, ">="&A1 & "<="&A2)
Would anyone know why the above equation is not work, many thanks.

=COUNTIF(C:C,">="&A1) - COUNTIF(C:C,">"&A2)


--ron

 

[Bob Phillips]

Ron's solution gives you what you want, but to answer your question, your
formula does not work as COUNTIF (and SUMIF) can only test against a single
value. If it could take multiple values, the format would be like an IF
test, along the lines of

AND(">="&A1,"<="&A2)

not simply stringing a range together.

--

HTH

RP
(remove nothere from the email address if mailing direct)