Another Try

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

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


Hope it helps.

Originally Posted by 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]
.
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

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

Cell N29 is not needed to complete the count.


[QUOTE=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

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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


[QUOTE=Biff]N29 is a formula cell and has nothing to do with any count.

Test it and you'll see what I mean.

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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.

Originally Posted by 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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I still do not understand what you mean. Can you explain a little
> more?
>
>
> Biff Wrote:
>> N29 is a formula cell and has nothing to do with any count.
>>
>> Test it and you'll see what I mean.
>>
>> Biff
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

See reply to your first post

Biff

"Krisjhn" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> N22 = SUMPRODUCT(--('Worksheet B'!J$2:J$528>=L22),--('Worksheet
> B'!J$2:J$528<L23)) (Copy down)
>
>
> Hope it helps.
>
>
> Krisjhn Wrote:
>> 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
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>

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

Test it and you'll see what I mean.

Biff

"Morrigan" <[email protected]> wrote in
message news:[email protected]...
>
> Cell N29 is not needed to complete the count.
>
>
> Biff Wrote:
>> 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
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=400958
>