# Thread: Sum of Range (Excel 2000)

1. ## 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

2. ## 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

3. ## 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

4. ## 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 1-52 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

5. ## 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

6. ## 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

7. ## 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

8. ## Re: Sum of Range (Excel 2000)

You are welcome. Happy to be of service.
Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•