Count Dates that meet date range 1

[eggohead]

Hello,

Im trying to count a column of dates in one sheet that meet the critera between two date values referenced in cells.

In this case count rows that fall between Nov1st 2008 till the end of the month which whould return 3 in the sample of 10 rows below.

Column:

=Dates!L:L

3/4/09 11:36
3/6/09 14:00
3/10/09 12:36
3/13/09 10:28
3/19/09 10:32
3/23/09 11:31
3/2/09 11:51
11/17/08 9:26
11/22/08 20:08
11/24/08 13:15

Criteria: >=B2 <= B3

B2 = 11/1/2008
B3 = 11/30/2008 11:59:00 PM

How do a formualte this? I have tried CountIF and Sum product to no avail...Maybe Im just formatting wrong. Any Help would be appreaciated.

- Thanks!

 

[SkipVought]

Hi,

Use the SUMPRODUCT function like
[tt]
=sumproduct(--($L$1:$L$999>=B2)*(($L$1:$L$999>=B3))
[/tt]
will not work on L:L

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

[tab]=countif(Dates!L:L, ">" & B2) - countif(Dates!L:L, ">" & B3)



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

oops, sinage inversion!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

..and can't spell signage!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[eggohead]

Thanks Skip!

so here is the formula:

=SUMPRODUCT(--(DATES!L2:L148>=B2)*((DATES!L2:L148>=B3)))

The only problem is this returns a value of 136? Theare are 147 rows so that would mean it finds 11 values. There are only 5 values that meet this criteris in this columen so it should return 5. What am I doing wrong?


Thanks

 

[eggohead]

Skip,

I must be smoking something. There was eleven values. Just didnt sort the whole list. Thanks I also corrected the sign as you mentioned so Im good.

=SUMPRODUCT(--(DATES!L2:L148>=B2)*((DATES!L2:L148<=B3)))

Thanks again,