Another Try

[Krisjhn]

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

In Worksheet Column J each date corresponds with a person. What I want
to do is get a count of dates/persons that occur during a set perid of
time, which is one week. The difficulty comes in, in that the column J
where the dates are entered span three different months. But I want to
break that down into weekly cohorts.

So for example if in *Worksheet B* in cell J2 I have a date that lies
between 8/17/05 and 8/23/05 then the number 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.

Here is roughly what my spreadsheet looks like

WORKSHEET A

......Column.....L[weeks]
Row
22 [8/17/05]
23 [8/24/05]

 

[Biff]

See reply to your first post

Biff

 

[Morrigan]

N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Workshee
B'!J$2:J$528<L23)) (Copy down)


Hope it helps.

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

In Worksheet Column J each date corresponds with a person. What I wan
to do is get a count of dates/persons that occur during a set perid o
time, which is one week. The difficulty comes in, in that the column
where the dates are entered span three different months. But I want t
break that down into weekly cohorts.

So for example if in *Worksheet B* in cell J2 I have a date that lie
between 8/17/05 and 8/23/05 then the number 1 would show up o
*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 wee
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 th
same column and then break that down by week to be reported on
different worksheet by week.

Here is roughly what my spreadsheet looks like

WORKSHEET

......Column.....L[weeks]
Row
22 [8/17/05]
23 [8/24/05]
.
29 [10/5/05]


WORKSHEET
.....Column.......J[Recieved Date]
Row
2 [8/19/05]
3 [10/6/05]
.
528 [9/14/05]

Thanks for your help.

Krisjh

 

[Biff]

Hi!

This formula will fail in cell N29.

The date criteria ends in cell L29. Using this formula the last reference
will be to cell L30. If L30 is empty or contains a text value the results
could be incorrect.

Biff

N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
B'!J$2:J$528<L23)) (Copy down)


Hope it helps.

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

In Worksheet Column J each date corresponds with a person. What I want
to do is get a count of dates/persons that occur during a set perid of
time, which is one week. The difficulty comes in, in that the column J
where the dates are entered span three different months. But I want to
break that down into weekly cohorts.

So for example if in *Worksheet B* in cell J2 I have a date that lies
between 8/17/05 and 8/23/05 then the number 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.

Here is roughly what my spreadsheet looks like

WORKSHEET A

......Column.....L[weeks]
Row
22 [8/17/05]
23 [8/24/05]
.
29 [10/5/05]


WORKSHEET B
.....Column.......J[Recieved Date]
Row
2 [8/19/05]
3 [10/6/05]
.
528 [9/14/05]

Thanks for your help.

Krisjhn

 

[Morrigan]

Cell N29 is not needed to complete the count.

Hi!

This formula will fail in cell N29.

The date criteria ends in cell L29. Using this formula the last
reference
will be to cell L30. If L30 is empty or contains a text value the
results
could be incorrect.

Biff

 

[Biff]

N29 is a formula cell and has nothing to do with any count.

Test it and you'll see what I mean.

Biff

 

[Morrigan]

I still do not understand what you mean. Can you explain a little
more?

 

[Biff]

Based on the OP's explanation:

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

So, N22 is the first formula cell:

N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
B'!J$2:J$528<L23)) (Copy down)

Your formula uses the next cell to set the upper boundry in the date range.
When you copy this down to N29 that formula would look like this:

=SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L29),--('WorksheetB'!J$2:J$528<L30))

L30 is outside the date range of L22:L29. So, if L30 is an empty cell it
evaluates to zero and the Sumproduct formula evaluates to:

--('WorksheetB'!J$2:J$528<0)

If you only copy the formula to cell N28 then you don't account for dates

=L29.

Biff

 

[Morrigan]

You are right. If you copy formula to cell N28 then you do not count
L29, which I think you should not. L29 should be consider as the first
day of next week. If you count L29 you will have 8 days in that week
which I think is wrong.