Using countif with a range of dates

[Guest]

Column Z contains multiple dates ranging over 10 years. I am trying to count
the number of cells in Column A that are between two dates (e.g., 01/01/07 -
01/31/07.)

I tried using
=SUM((COUNTIF(Sheet2!Z:Z,"<01/31/07"))-(COUNTIF(Sheet2!Z:Z,">01/01/07")))

however, the count if not correct. The result is 3,172 via the above formula
which is wrong. Using a filter, I know the correct result is 56, but I
cannot correct my formula to reflect.

Many Thanks!

 

[Guest]

=COUNTIF(Sheet2!Z:Z,"<="&01/31/07)-COUNTIF(Sheet2!Z:Z,"<"&01/01/07)

 

[Guest]

Thanks, but that didn't work. The result was 0. I'm curious about the use
of the "&" as well as using the < > inbetween the " marks.

 

[Ragdyer]

Try this:

=COUNTIF(Z:Z,">=1/1/07")-COUNTIF(Z:Z,">1/31/07")

 

[Dave Peterson]

=COUNTIF(Sheet2!Z:Z,"<="&DATE(2007,1,31))-COUNTIF(Sheet2!Z:Z,"<"&DATE(2007,1,1))

You could also use this kind of formula (but not the whole column until xl2007):

=sumproduct(--(text(sheet2!z1:z999,"yyyymm")="200701"))

 

[Guest]

Thank you!

Try this:

=COUNTIF(Z:Z,">=1/1/07")-COUNTIF(Z:Z,">1/31/07")