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.

 I have a list of dates that I need to count based on a date range
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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

#1
November 24th 04, 01:05 PM
 ejb030353 external usenet poster Posts: n/a
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
November 24th 04, 01:16 PM
 Johannes external usenet poster Posts: n/a

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
 Jack Schitt external usenet poster Posts: n/a

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
 Jack Schitt external usenet poster Posts: n/a

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
 ejb030353 external usenet poster Posts: n/a

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 Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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

All times are GMT +1. The time now is 07:39 AM.