+ Reply to Thread
Results 1 to 7 of 7

Moving Sum Lookup

  1. #1
    Registered User
    Join Date
    04-30-2009
    Location
    Gretna, Nebraska
    MS-Off Ver
    Microsoft 365 Apps for Business
    Posts
    46

    Moving Sum Lookup

    On the attached, I want to cllick on the yellow 2009 drop downs, select a starting and ending date from this year, have it populate a total from how ever many weeks are selected. I then want it to look up the same week range during the previous year for a growth comparison. Is this possible?
    Attached Files Attached Files
    Last edited by DButtgen; 05-01-2009 at 11:30 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Moving Sum Lookup

    Since the weeks don't match from year to year, you want the comparison to occur straight across one row? So 1/2/2009 would compare to 1/4/2008?

    If so, the two formula are:

    B6:
    =SUMPRODUCT(--($D$2:$D$100>=$A$4),--($D$2:$D$100<$B$4),E2:E100+F2:F100+G2:G100+H2:H100+I2:I100)

    B7:
    =SUMPRODUCT(--($D$2:$D$100>=$A$4),--($D$2:$D$100<$B$4),L2:L100+M2:M100+N2:N100+O2:O100+P2:P100)

    B8:
    =IF(AND(B7<>"",B6<>""),B6/B7-1,"") (formatted as percentage)
    Last edited by JBeaucaire; 05-01-2009 at 01:48 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Moving Sum Lookup

    Sorry, those formulas give the totals for ALL the stores together, all at once. Perhaps that will come in handy, but I just noticed the Selection Box in B1 for one store. So, the corrected formulas would be:

    B6:
    =SUMPRODUCT(--($D$2:$D$100>=$A$4),--($D$2:$D$100<$B$4),INDIRECT(ADDRESS(2,MATCH(B1,Branch_Name,0)+4)&":"&ADDRESS(100,MATCH(B1,Branch_Name,0)+4)))

    B7:
    =SUMPRODUCT(--($D$2:$D$100>=$A$4),--($D$2:$D$100<$B$4),INDIRECT(ADDRESS(2,MATCH(B1,Branch_Name,0)+11)&":"&ADDRESS(100,MATCH(B1,Branch_Name,0)+11)))

  4. #4
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: Moving Sum Lookup

    Here's another way.

    B6:
    =SUMPRODUCT((Date_2009>=A4)*(Date_2009<=B4)*(Branch_Name=B1),E2:I53)

    B7:
    =SUMPRODUCT((Date_2009>=A4)*(Date_2009<=B4)*(L1:P1=B1),L2:P53)

    B8:
    =IF(AND(B7,B6),B6/B7-1,0)
    Regards

    Jon (Excel 2003, 2007, 2010, 2013)

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Moving Sum Lookup

    Good catch on the named ranges, I didn't even notice them in the OP's sheet.

    I get significantly different results than you do. His dates represent the beginning of weeks, so I believe the <=B4 references should be <B4 so that it doesn't add in the following week's numbers.

  6. #6
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: Moving Sum Lookup

    Quote Originally Posted by JBeaucaire View Post
    Good catch on the named ranges, I didn't even notice them in the OP's sheet.

    I get significantly different results than you do. His dates represent the beginning of weeks, so I believe the <=B4 references should be <B4 so that it doesn't add in the following week's numbers.
    Ah that's a good point...

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Moving Sum Lookup

    Thanks for the feedback!

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1