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$15>A$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
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
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$15>B$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
-----------------------------------------------------------
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$1000>B$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
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$1000>B$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
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$1000>B$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
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
