I have a list of dates that I need to count based on a date range
 

I have a date range that contains 60000 records, I need to count this range
and return a value for the number found. i.e. number of records between
10/01/02 and 10/30/02

If your data is in A1:A6000 then one way is to put your 2 dates in cells E1
and E2 and then use
= COUNTIF(A1:A6000; "<" & E2) - COUNTIF(A1:A6000; "<" & E1) - 1

You might want to check on the "<" versus "<=" and the "-1" at the and. Is a
little dependant on if you wish to in- or exclude the boundaries.

Good luck
JP


"ejb030353" schreef in bericht
...
I have a date range that contains 60000 records, I need to count this
range
and return a value for the number found. i.e. number of records between
10/01/02 and 10/30/02

There may be a more efficient solution. I have not tried this over 60K
records, but in my example the population range is contained in C15:C36, the
lower bound date in C9 and upper bound in C10.

=SUMPRODUCT(--(C15:C36=C9),--(C15:C36<=C10))


"ejb030353" wrote in message
...
I have a date range that contains 60000 records, I need to count this
range
and return a value for the number found. i.e. number of records between
10/01/02 and 10/30/02

Forget that, JP's solution much more efficient.

"Jack Schitt" wrote in message
...
There may be a more efficient solution. I have not tried this over 60K
records, but in my example the population range is contained in C15:C36,
the
lower bound date in C9 and upper bound in C10.

=SUMPRODUCT(--(C15:C36=C9),--(C15:C36<=C10))


"ejb030353" wrote in message
...
I have a date range that contains 60000 records, I need to count this
range
and return a value for the number found. i.e. number of records between
10/01/02 and 10/30/02

Thanks,

This was a big help.

"Johannes" wrote:

If your data is in A1:A6000 then one way is to put your 2 dates in cells E1
and E2 and then use
= COUNTIF(A1:A6000; "<" & E2) - COUNTIF(A1:A6000; "<" & E1) - 1

You might want to check on the "<" versus "<=" and the "-1" at the and. Is a
little dependant on if you wish to in- or exclude the boundaries.

Good luck
JP


"ejb030353" schreef in bericht
...
I have a date range that contains 60000 records, I need to count this
range
and return a value for the number found. i.e. number of records between
10/01/02 and 10/30/02