Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 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
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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