Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ejb030353
 
Posts: n/a
Default 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
  #2   Report Post  
Johannes
 
Posts: n/a
Default

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



  #3   Report Post  
Jack Schitt
 
Posts: n/a
Default

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



  #4   Report Post  
Jack Schitt
 
Posts: n/a
Default

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





  #5   Report Post  
ejb030353
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I sum information in a list with a date range? Dave Excel Worksheet Functions 2 November 23rd 04 09:17 PM
Using formulas to determine date in one cell based on date in anot Gary Excel Worksheet Functions 2 November 22nd 04 09:11 AM
Excel: How to return count for each cell within date range criter. Louisa Excel Worksheet Functions 0 November 5th 04 12:58 PM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 0 October 28th 04 08:03 PM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 1 October 28th 04 09:52 AM


All times are GMT +1. The time now is 10:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"