How to count dates within a certain range in a column with mutiple date range entries

  • Thread starter Thread starter Krisjhn
  • Start date Start date
K

Krisjhn

On my speedsheet I have two worksheets A and B

WORKSHEET

Column J2:J528 entitled -1st RCVD Date-

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

WORKSHEET

Column N21 titled -# of Persons-
Column L22:L29 is Titled Weeks - Each row is broken down by wee
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 r1s
received.

So for example if in Worksheet B in cell J2 I have a date that lie
between 8/17/05 and 8/23/05 a 1 would show up on Workseet A in #o
persons in the row for that corresponding week. Say the next week
have 6 entires of dates that land in that next week then the number
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 th
same column and then break that down by week to be reported on
different worksheet by week.

Thanks for your help.

Krisjh
 
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
 
Back
Top