Results 1 to 8 of 8
Thread: Sum of Range (Excel 2000)

20030107, 10:11 #1
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sum of Range (Excel 2000)
I have a summary sheet and and input sheet with sales per branch. I would like to have a dynamic sum formula that will:
1. Lookup the specific branch sales
2. The week will be selected by the user. The Month to Date sum should be the sum for the week for the month that the week selected is in.
3. The Year to date should be all the weeks till the week selected.
Refer example attached
Thanks

20030107, 11:18 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum of Range (Excel 2000)
Your example is not explicit enough: This Can be done with several techniques, but it is dependent on how the INPUT sheet is setup to get the "week" and "Month" info.
Some questions off the top of my head:
Do you actually have the dates somewhere?
is it like you have implied:
4 weeks / month, 12 months /year with no deviation (what happens to the other weeks?)
Is there some other means to know when a new month starts
What happens when a week is in more than one month?
How do you define a week when it crosses a year? US does it different than Europe. Is it part of current year/past year? how do you define?
Steve

20030107, 11:45 #3
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of Range (Excel 2000)
Your example is not explicit enough: This Can be done with several techniques, but it is dependent on how the INPUT sheet is setup to get the "week" and "Month" info.
Some questions off the top of my head:
Do you actually have the dates somewhere? is it like you have implied:
4 weeks / month, 12 months /year with no deviation (what happens to the other weeks? The dates will be as per the Julian Calender refer attached modified workbook.
What happens when a week is in more than one month? As per Julian schedule, it can only be in 1 month
How do you define a week when it crosses a year? US does it different than Europe. Is it part of current year/past year? how do you define?
The whole spreadsheet will be week driven. The user will selected the week and as per his selection I need the sum function to change for the MTD and YTD
Thanks for your response

20030107, 21:20 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum of Range (Excel 2000)
Try this
I modified your INPUT to add a row of MONTHS from the Julian Cal sheet
I modified your "week" row so that they are actually the numbers 152 but they are formatted to display Week1  week52
The modified summary uses something like:
Week:
=INDEX('Input Modified'!B4:BA4,$B$2)
MTD: Sums all <= currrent week  sum of everything BEFORE the current month started
=SUMIF('Input Modified'!$B$3:$BA$3,+"<="&$B$2,'Input Modified'!B4:BA4)SUMIF('Input Modified'!$B$3:$BA$3,+"<="&MATCH(INDEX('Input Modified'!$B$1:$BA$1,$B$2),'Input Modified'!$B$1:$BA$1,0)1,'Input Modified'!B4:BA4)
YTDsums all that are <= current week
=SUMIF('Input Modified'!$B$3:$BA$3,+"<="&$B$2,'Input Modified'!B4:BA4)
Steve

20030108, 07:43 #5
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of Range (Excel 2000)
Thank you Steve. The formula looks impressive.
Now I have another condition. The branch numbers on the input and summary will be in differant sequence. So I need to have a vlookup or ? to supply the sales as per the branch no in the summary sheet. I hope I'm clear enough.
Thanks again
Mario

20030108, 11:15 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum of Range (Excel 2000)
1) sort the input in the order desired
Or if not possible:
I think the easiest way to set it up is to to have a separate worksheet that grabs the Branch number, the Week, MTD, YTD from each in the order on the Input sheet using the calculations I list
Then have the summary sheet use MATCH with a given branch # to find the row in this NEW sheet, then use INDEX of the dataset on the new sheet, and the match (for the row#) to get the separate columns.
Steve

20030108, 14:08 #7
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of Range (Excel 2000)
Thanks for the help. I got it right by putting all the weeks in one sheet(input) and then just another 2 columns (MTD & YTD). I used your code to generate the values for the 2 columns. AND finally did a VLOOKUP from the input to the summary sheet. Thanks for the directions and sample code

20030108, 16:23 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sum of Range (Excel 2000)
You are welcome. Happy to be of service.
Steve