|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 24th 04, 01:05 PM
|
|||
|
|||
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 
|
| Ads |
|
#2
November 24th 04, 01:16 PM
|
|||
|
|||
|
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
November 24th 04, 01:23 PM
|
|||
|
|||
|
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
November 24th 04, 01:35 PM
|
|||
|
|||
|
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
November 24th 04, 02:27 PM
|
|||
|
|||
|
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 > > >
|
| Thread Tools | |
| Display Modes | |
|
|
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 08:17 PM |
| Using formulas to determine date in one cell based on date in anot | Gary | Excel Worksheet Functions | 2 | November 22nd 04 08:11 AM |
| Excel: How to return count for each cell within date range criter. | Louisa | Excel Worksheet Functions | 0 | November 5th 04 11:58 AM |
| 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 |
Linear Mode
