count number of dates in a column that are within a month period..

[Sarah J]

I'm trying to write a function that will look at a column of dates (C2:C72)
and count the number that are between Nov 01 2007 and Nov 30 2007 (e.g.)

I'm thinking that LOOKUP or IF function might work..

=LOOKUP("11/01/2007<date<11/31/2007",C2:C12) (this one doesn't count, and I
can't figure out why it is not reading my dates as dates)....

thanks much!

 

[Mike H]

Format as general, Try:-

=COUNTIF(C2:C12,">1/11/2007")-COUNTIF(C2:C12,">30/11/2007")

Mike

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(YEAR(C2:C72)=2007),--(MONTH(C2:C72)=11))

(Note that there is no 11/31/2007...)

 

[Toppers]

=SUMPRODUCT(--(MONTH(C2:C72)=11))

if only one year

 

[PCLIVE]

If you truely mean "between" those two dates, then perhaps:
=SUMPRODUCT(--(C2:C72>39387),--(C2:C72<39416))

If you want to include those two dates, then:
=SUMPRODUCT(--(C2:C72>=39387),--(C2:C72<=39416))

HTH,
Paul

 

[JE McGimpsey]

Note that this only works in the 1900 date format...

 

[Dave Peterson]

One more:

=sumproduct(--(text(c2:c72,"yyyymm")="200711"))

 

[T. Valko]

Another one:

E1 = 11/1/2007
F1 = 11/30/2007

=INDEX(FREQUENCY(C2:C12,E1:F1-{1,0}),2)

Biff