| 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 |
|
#8
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#9
|
|||
|
|||
|
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. Biff Wrote: 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" wrote in message ... 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 -- 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 | |
|
|
Linear Mode
