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

[ejb030353]

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

 

[Johannes]

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

 

[Jack Schitt]

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))

 

[Jack Schitt]

Forget that, JP's solution much more efficient.

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]

Thanks,

This was a big help.

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