Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Krisjhn
 
Posts: n/a
Default How to count dates within a certain range in a column with mutiple date range entries


On my speedsheet I have two worksheets A and B

WORKSHEET B

Column J2:J528 entitled -1st RCVD Date-

In this column I will have a variety of dates spanning over three
diffferent months

WORKSHEET A

Column N21 titled -# of Persons-
Column L22:L29 is Titled Weeks - Each row is broken down by week
L22/8/17/05, L23 8/24/05. L24 8/31/05, etc to L29 10/5/05

What I am looking for is a count of number of persons by week r1st
received.

So for example if in Worksheet B in cell J2 I have a date that lies
between 8/17/05 and 8/23/05 a 1 would show up on Workseet A in #of
persons in the row for that corresponding week. Say the next week I
have 6 entires of dates that land in that next week then the number 6
would show up on worksheet A on the row for 8/24/05. And so on.

So the question is how do I add up dates within a certain range in the
same column and then break that down by week to be reported on a
different worksheet by week.

Thanks for your help.

Krisjhn


--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile: http://www.excelforum.com/member.php...o&userid=26796
View this thread: http://www.excelforum.com/showthread...hreadid=400925

  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Try this formula in N22:

=SUMPRODUCT(--(Sheet2!J$2:J$528=L22),--(Sheet2!J$2:J$528<=L22+6))

Copy down to N29.

This assumes that you are using true dates and not just text strings that
look like dates.

Biff

"Krisjhn" wrote in
message ...

On my speedsheet I have two worksheets A and B

WORKSHEET B

Column J2:J528 entitled -1st RCVD Date-

In this column I will have a variety of dates spanning over three
diffferent months

WORKSHEET A

Column N21 titled -# of Persons-
Column L22:L29 is Titled Weeks - Each row is broken down by week
L22/8/17/05, L23 8/24/05. L24 8/31/05, etc to L29 10/5/05

What I am looking for is a count of number of persons by week r1st
received.

So for example if in Worksheet B in cell J2 I have a date that lies
between 8/17/05 and 8/23/05 a 1 would show up on Workseet A in #of
persons in the row for that corresponding week. Say the next week I
have 6 entires of dates that land in that next week then the number 6
would show up on worksheet A on the row for 8/24/05. And so on.

So the question is how do I add up dates within a certain range in the
same column and then break that down by week to be reported on a
different worksheet by week.

Thanks for your help.

Krisjhn


--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile:
http://www.excelforum.com/member.php...o&userid=26796
View this thread: http://www.excelforum.com/showthread...hreadid=400925



  #3   Report Post  
Krisjhn
 
Posts: n/a
Default


Thanks Biff. Worked like a charm.


--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile: http://www.excelforum.com/member.php...o&userid=26796
View this thread: http://www.excelforum.com/showthread...hreadid=400925

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
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM
Compare range of dates and sum... SAL Excel Discussion (Misc queries) 5 March 22nd 05 03:59 PM
Find (Today-21) in a range of dates JG Excel Discussion (Misc queries) 4 March 15th 05 04:59 PM
Sum between a range of dates Bruce Excel Worksheet Functions 3 January 5th 05 03:25 AM
Excel: How to return count for each cell within date range criter. Louisa Excel Worksheet Functions 0 November 5th 04 12:58 PM


All times are GMT +1. The time now is 09:05 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"