Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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] |
#2
|
|||
|
|||
See reply to your first post
Biff "Krisjhn" wrote in message ... On my speedsheet I have two worksheets A and B WORKSHEET B Column J2:J528 entitled -1st RCVD Date- In this column I will have a variety of dates spanning over three diffferent months WORKSHEET A Column N21 titled -# of Persons- Column L22:L29 is titled -Weeks- - Each row is broken down by week L22/8/17/05, L23 8/24/05. L24 8/31/05, etc to L29 10/5/05 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] |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
N29 is a formula cell and has nothing to do with any count.
Test it and you'll see what I mean. Biff "Morrigan" wrote in message ... 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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|