+ Reply to Thread
Results 1 to 3 of 3

Dynamic sumif ranges based on Hlookup criteria

  1. #1
    Registered User
    Join Date
    01-15-2010
    Location
    Scottsdale
    MS-Off Ver
    Excel 2010
    Posts
    72

    Dynamic sumif ranges based on Hlookup criteria

    I am looking to add a sumif formula that has the ability to dynamically adjust the "sum range" as my source data shifts colums. The issue that I am having is that my summary page will have multiple full years layed out but my source data is adjusting the months populated as the year progresses. In my example, I have source data 8-11, however, next month when updated month 12 will come into the source data (represented in column F greyed out). I need my summary sumif formula to automaticall move the sum_range to column F for the actual sums and likewise I need the "estimate" sum range for month 10 to move to column G.

    I was thinking of some sort of offset/index/hlookup on named columns but not wure what that would look like.

    Let me know you thoughts.

    Regards.
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Dynamic sumif ranges based on Hlookup criteria

    Hello,

    how do you populate the Source Data sheet? Do you insert a new column every month?
    In the Summary sheet, K is listed twice. What would be the expected result?

  3. #3
    Registered User
    Join Date
    01-15-2010
    Location
    Scottsdale
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Dynamic sumif ranges based on Hlookup criteria

    Quote Originally Posted by teylyn View Post
    Hello,

    how do you populate the Source Data sheet? Do you insert a new column every month?
    In the Summary sheet, K is listed twice. What would be the expected result?
    Thanks for the response, actually the letters listed down the colums is not a colums reference, that could be a color, a reference # or whatever. That was a bad choice of reference criteria on my part, sorry for the confusion. I actually just figured it out. I actually used

    SUMIF('Act CAE'!$B$37:$B$800,Summary!$D10,OFFSET('Act CAE'!$K$37,,HLOOKUP(J$2,'Act CAE'!$K$34:$IV$36,3,FALSE)):OFFSET('Act CAE'!K$800,,HLOOKUP(J$2,'Act CAE'!$K$34:$IV$36,3,FALSE)))

    The "criteria range" is fixed, and for the dynamic sum_range I used a reference cell offset by the number of columns a hlookup formula returned.

    So this one is solved. But non the less, thank you for taking the time!

    Cheers


    P.S. Mod, do I need to "mark" this as solved? Delete it? Thanks for getting me acquainted to proper forum etiquette
    Last edited by Dial1; 10-24-2011 at 01:27 AM.

+ 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