Excel - looking up a date range 1

[leewisener]

Hello,

I have a column of dates and what I want to do is search that column and count the number of dates that lie within a certain range.

for example:

date range 01/01/2004 - 01/01/2005

I want to search the 400 or so dates in my column of dates where there are dates that fall in the range above.

Any ideas?

Thanks

Lee

 

[GlennUK]

How about using 2 COUNTIFs and using the difference ...

=COUNTIF(myrange,"<01/01/2005")-COUNTIF(myrange,"<01/01/2004")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[leewisener]

Thats fine for the example above but it as only 1 example my date range could be 01/01/05 - 31/01/05.

 

[leewisener]

This works fine to count all dates less than 31/01/05

=COUNTIF('2004'!H:H, "<31/01/05")

Really I need a simple solution like:

=COUNTIF('2004'!H:H, ">01/01/05" AND "<31/01/05")

But that would be too easy

 

[SkipVought]

Try using SUMPRODUCT
[tt]
=SUMPRODUCT(('2004'!H:H,>$A$1)*('2004'!H:H,<$B$1))
[/tt]
where A1 contains date 01/01/05 and B1 contains date 31/01/05

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

my date range could be 01/01/05 - 31/01/05.

What difference would that make ?

=COUNTIF(myrange,"<31/01/2005")-COUNTIF(myrange,"<01/01/2005")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[arthurbr]

Maybe using a pivot table and grouping the dates in the range you want would do the trick?

 

[leewisener]

What difference would that make ?

=COUNTIF(myrange,"<31/01/2005")-COUNTIF(myrange,"<01/01/2005")

Rgds, Geoff


Your absolutely right, it makes no difference and works perfectly!

 

[GlennUK]

Thank goodness you tried it at last.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.