+ Reply to Thread
Results 1 to 8 of 8

SUMIFS Using VLOOKUP to Define SUMRANGE

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    SUMIFS Using VLOOKUP to Define SUMRANGE

    I want to add a SUMIFS formula to the 'Main' sheet but using a VLOOKUP to define the reference for the SUMRANGE column on the 'Data' sheet. The value for the lookup is controlled by the entry in cell H1 on the 'Main' tab, so if H1 = 1, the SUMRANGE is column A on the Data tab and likewise if H1 = 6 then the SUMRANGE is column F on the Data tab and so on. So I'm trying to say something like:

    Please Login or Register  to view this content.
    But I can't figure out the correct syntax. Ideally the SUMRANGE should be effectively row2 to the last row of data on the Data tab.

    Many thanks
    Last edited by HangMan; 04-26-2015 at 05:41 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    There are other ways, but try this...
    =SUM(OFFSET(Data!$A$1,,MATCH("Month "&Main!$H$1,Data!$A$1:$L$1,0)-1,COUNT(Data!$A:$A,1),1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    Hi Again,

    Would this also work as a SUMIFS formula because the 'Rest of Formula' element refers to additional 'criteria ranges' and 'criteria'. I'm trying to also figure out how the MATCH("Month... part works as the month is specific to the lookup, i.e. Month 1 is April (not January) and so on...

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    For the "rest of formula" part, see if this helps...
    A
    B
    C
    1
    Month 1
    Month 2
    2
    aa
    23
    45
    3
    bb
    45
    56
    4
    cc
    576
    45
    5
    dd
    78
    3
    6
    ee
    80
    345
    7
    aa
    56
    57
    8
    bb
    342
    79
    9
    cc
    34
    6
    10
    dd
    556
    745
    11
    ee
    767
    23
    12
    aa
    78
    34
    13
    bb
    789
    46
    14
    cc
    5
    67
    15
    dd
    645
    79
    16
    ee
    345
    89
    17
    aa
    34
    674

    =SUMIF(Data!$A:$A,"aa",OFFSET(Data!$B$1,,MATCH("Month "&Main!$H$1,Data!$B$1:$M$1,0)-1,COUNT(Data!$B:$B,1),1))

    for the MATCH part, if you use real month names (in row 1 in DATA and in Main H1), then it should still work

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    Hi again,

    Will take a look at this in the morning as it is late here and come back to you...

    Many thanks

  6. #6
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    Hi,

    I've slightly updated the attachment to demonstrate what I'm trying to achieve.

    Unfortunately the Month 1, Month 2 etc. are fixed and can't be changed in the context of what I'm trying to do. I also can't add the additional a column for 'aa', 'bb', 'cc' etc to the main sheet as the sheet is very much fixed. So basically instead of saying:

    Please Login or Register  to view this content.
    I need the column for the SUMRANGE part of the SUMIFS (Data!$C$2:$C$17) to be determined by a lookup (or similar) which is controlled by the entry in Main!$H$1

    So something like this, only this doesn't work...

    Please Login or Register  to view this content.
    So if Main!$H$1 = 3 then the SUMRANGE in the SUMIFS formula will sum column E.

    I'm basically taking two elements, Data! and $C$2:$C$17 but trying to use a lookup to determine $C$2:$C$17 so that the result is Data!$C$2:$C$17. Ideally the range should be just from row 2 to the last row containing data (which is I guess where an offset would come in).

    Even though I have Data! and my lookup gives me $C:$C, the formual errors.

    Apologies if what you've already provided works, but I haven't succeeded in getting it to work.

    Many thanks
    Last edited by HangMan; 04-26-2015 at 05:44 AM.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    INDEX can return a whole column or row of a range, so try this formula

    =SUMIFS(INDEX(Data!C2:N17,0,H1),Data!A2:A17,"North",Data!B2:B17,"Red")

    The zero indicates that you want the whole column, within the specified range, and H1 indicates which column
    Audere est facere

  8. #8
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: SUMIFS Using VLOOKUP to Define SUMRANGE

    Hi daddylonglegs,

    That worked perfectly...

    Many thanks for taking the time to help...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SumIf with sumrange in multiple variable columns
    By rpinxt in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-19-2015, 09:10 AM
  2. Sumifs & Dynamic Sumrange
    By amartin575 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2013, 10:02 AM
  3. Value in a cell to define an array for VLOOKUP
    By Awesomeau in forum Excel General
    Replies: 4
    Last Post: 07-07-2011, 08:04 AM
  4. How to sumif with multiple range and single sumrange
    By helpplease! in forum Excel General
    Replies: 4
    Last Post: 11-05-2007, 11:53 AM
  5. how to use VB code to define vlookup in two workbook
    By yihong in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2005, 10:59 PM

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