Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sum information in a list with a date range? | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Excel: How to return count for each cell within date range criter. | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |