| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Hello!
1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Hi
Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Roger,
I followed your instructions but the result is zero for each week: WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt 0 0 0 0 0 0 0 The formula I used: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2))*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000))) If you can please shed some light on the error. Shahid "Roger Govier" wrote in message ... Hi Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Hi Shahid
My apologies. I entered one too many brackets in my formula, Excel then tried to correct for you by adding a matching one, but in the wrong place. Try the following =SUMPRODUCT( ('Raw Data Sheet'!$A$1:$A$1000B$2)* ('Raw Data Sheet'!$A$1:$A$1000<=C$2)* ('Raw Data Sheet'!$D$1:$D$1000) ) I have set it out this way so you can see there is an outer set of brackets for Sumproduct( ) and one set around each set of conditions -- Regards Roger Govier "SSJ" wrote in message ... Roger, I followed your instructions but the result is zero for each week: WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt 0 0 0 0 0 0 0 The formula I used: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2))*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000))) If you can please shed some light on the error. Shahid "Roger Govier" wrote in message ... Hi Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Roger,
I am not sure what I am missing out something here, so I am copying, once again. 1) I reduced the range from 1000 to 15 to make it easy. 2) I have checked the formatting for the dates and the amount. 3) In tried typing in the range in the formula and in the other instance i selected the range by using the mouse and going in the "Raw Data Sheet". Nothing seems to work Shahid SUMMARY SHEET WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! The formula being used is: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$15B$2)*('Raw Data Sheet'!$A$1:$A$15<=C$2)*('Raw Data Sheet'!$D$1:$D$15)) RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 09/30/06 Sat 0.00 10/01/06 Sun 0.00 10/02/06 Mon 100.00 10/03/06 Tue 110.00 10/04/06 Wed 120.00 10/05/06 Thu 130.00 10/06/06 Fri 140.00 10/07/06 Sat 0.00 10/08/06 Sun 0.00 10/09/06 Mon 200.00 10/10/06 Tue 210.00 10/11/06 Wed 220.00 10/12/06 Thu 230.00 10/13/06 Fri 240.00 ----------------------------------------------------------- "Roger Govier" wrote in message ... Hi Shahid My apologies. I entered one too many brackets in my formula, Excel then tried to correct for you by adding a matching one, but in the wrong place. Try the following =SUMPRODUCT( ('Raw Data Sheet'!$A$1:$A$1000B$2)* ('Raw Data Sheet'!$A$1:$A$1000<=C$2)* ('Raw Data Sheet'!$D$1:$D$1000) ) I have set it out this way so you can see there is an outer set of brackets for Sumproduct( ) and one set around each set of conditions -- Regards Roger Govier "SSJ" wrote in message ... Roger, I followed your instructions but the result is zero for each week: WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt 0 0 0 0 0 0 0 The formula I used: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2))*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000))) If you can please shed some light on the error. Shahid "Roger Govier" wrote in message ... Hi Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
Hi
If you want to mail a copy of the workbook direct to me I'll take a look Remove NOSPAM form my email address to mail direct. -- Regards Roger Govier "SSJ" wrote in message ... Roger, I am not sure what I am missing out something here, so I am copying, once again. 1) I reduced the range from 1000 to 15 to make it easy. 2) I have checked the formatting for the dates and the amount. 3) In tried typing in the range in the formula and in the other instance i selected the range by using the mouse and going in the "Raw Data Sheet". Nothing seems to work Shahid SUMMARY SHEET WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! The formula being used is: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$15B$2)*('Raw Data Sheet'!$A$1:$A$15<=C$2)*('Raw Data Sheet'!$D$1:$D$15)) RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 09/30/06 Sat 0.00 10/01/06 Sun 0.00 10/02/06 Mon 100.00 10/03/06 Tue 110.00 10/04/06 Wed 120.00 10/05/06 Thu 130.00 10/06/06 Fri 140.00 10/07/06 Sat 0.00 10/08/06 Sun 0.00 10/09/06 Mon 200.00 10/10/06 Tue 210.00 10/11/06 Wed 220.00 10/12/06 Thu 230.00 10/13/06 Fri 240.00 ----------------------------------------------------------- "Roger Govier" wrote in message ... Hi Shahid My apologies. I entered one too many brackets in my formula, Excel then tried to correct for you by adding a matching one, but in the wrong place. Try the following =SUMPRODUCT( ('Raw Data Sheet'!$A$1:$A$1000B$2)* ('Raw Data Sheet'!$A$1:$A$1000<=C$2)* ('Raw Data Sheet'!$D$1:$D$1000) ) I have set it out this way so you can see there is an outer set of brackets for Sumproduct( ) and one set around each set of conditions -- Regards Roger Govier "SSJ" wrote in message ... Roger, I followed your instructions but the result is zero for each week: WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt 0 0 0 0 0 0 0 The formula I used: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2))*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000))) If you can please shed some light on the error. Shahid "Roger Govier" wrote in message ... Hi Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
File received.
My problem, I had forgotten that row 1 of Raw Data was headers. Formula amended to =SUMPRODUCT(('Raw Data Sheet'!$A$2:$A$15A$2)* ('Raw Data Sheet'!$A$2:$A$15<=B$2)* ('Raw Data Sheet'!$D$2:$D$15)) and all is working fine on file sent back to Shahid -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you want to mail a copy of the workbook direct to me I'll take a look Remove NOSPAM form my email address to mail direct. -- Regards Roger Govier "SSJ" wrote in message ... Roger, I am not sure what I am missing out something here, so I am copying, once again. 1) I reduced the range from 1000 to 15 to make it easy. 2) I have checked the formatting for the dates and the amount. 3) In tried typing in the range in the formula and in the other instance i selected the range by using the mouse and going in the "Raw Data Sheet". Nothing seems to work Shahid SUMMARY SHEET WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! The formula being used is: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$15B$2)*('Raw Data Sheet'!$A$1:$A$15<=C$2)*('Raw Data Sheet'!$D$1:$D$15)) RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 09/30/06 Sat 0.00 10/01/06 Sun 0.00 10/02/06 Mon 100.00 10/03/06 Tue 110.00 10/04/06 Wed 120.00 10/05/06 Thu 130.00 10/06/06 Fri 140.00 10/07/06 Sat 0.00 10/08/06 Sun 0.00 10/09/06 Mon 200.00 10/10/06 Tue 210.00 10/11/06 Wed 220.00 10/12/06 Thu 230.00 10/13/06 Fri 240.00 ----------------------------------------------------------- "Roger Govier" wrote in message ... Hi Shahid My apologies. I entered one too many brackets in my formula, Excel then tried to correct for you by adding a matching one, but in the wrong place. Try the following =SUMPRODUCT( ('Raw Data Sheet'!$A$1:$A$1000B$2)* ('Raw Data Sheet'!$A$1:$A$1000<=C$2)* ('Raw Data Sheet'!$D$1:$D$1000) ) I have set it out this way so you can see there is an outer set of brackets for Sumproduct( ) and one set around each set of conditions -- Regards Roger Govier "SSJ" wrote in message ... Roger, I followed your instructions but the result is zero for each week: WE WE WE WE WE WE WE 09/29/06 10/06/06 10/13/06 10/20/06 10/27/06 11/03/06 11/10/06 11/17/06 Cash Receipt 0 0 0 0 0 0 0 The formula I used: =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2))*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000))) If you can please shed some light on the error. Shahid "Roger Govier" wrote in message ... Hi Assuming the heading Cash Receipt on Summary is in cell A3 In cell A2 enter 09/29/2006 Format the cell with Font White so it doesn't show. In B2 enter =SUMPRODUCT(('Raw Data Sheet'!$A$1:$A$1000B$2)*('Raw Data Sheet'!$A$1:$A$1000<=C$2)*((('Raw Data Sheet'!$D$1:$D$1000)) Copy across through C2:F2 -- Regards Roger Govier "SSJ" wrote in message ... Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a list form on a daily basis. Any date may have multiple entries due to different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in RAW DATA SHEET. Basically the summary sheet is summing based on the date grouped in weeks. RAW DATA SHEET DATE DAY CUSTOMER AMOUNT 9/30/2006 Sat 10/1/2006 Sun 10/2/2006 Mon A 100 10/2/2006 Mon K 110 10/3/2006 Tue B 120 10/4/2006 Wed A 130 10/5/2006 Thu C 140 10/6/2006 Fri B 150 10/7/2006 Sat 10/8/2006 Sun 10/9/2006 Mon A 200 10/10/2006 Tue K 210 10/11/2006 Wed B 220 10/12/2006 Thu A 230 10/13/2006 Fri C 240 SUMMARY SHEET Week Ending Week Ending Week Ending Week Ending 10/6/2006 10/13/2006 10/20/2006 10/27/2006 Cash Receipt 750.00 1,100.00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro | Excel Discussion (Misc queries) | |||
| Summarizing data on one main sheet | Excel Worksheet Functions | |||
| Averages based on dates | Excel Worksheet Functions | |||
| Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Linear Mode
