+ Reply to Thread
Results 1 to 3 of 3

SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    São Paulo, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable

    Hi all,

    I'm facing the following problem: I need to use the SUMIFS function to add data that meets specific chriteria from a database into one cell in another worksheet.

    I was wondering if it's possible to turn the "sum_range" argument of that funcion into a variable, that is, something like "all the cells which are under the following title on the database: JANUARY 2013" or something like that.

    The database includes data for "Current Year's January", "Current Year's February", "Current Year's March" and so forth. It also includes data for "Previous Year's Jan", "Previous Year's Feb", and so on. These are the column titles of the database.

    In the sheet where the sumifs is supposed to be returned, I'd like to use the same titles on a row at the top, and make the range that's supposed to be summed up, that is, "sum_range", equal to "all the cells below that specific title, but on the database sheet". I imagine doing something involving the HLOOKUP function, but can't precisely figure out how.

    If you need further explanation of the problem, please let me know.

    Many thanks in advance,
    Bruno

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable

    Bruno,

    Welcome to the forum!
    You would use Sumifs(Index(table,0,Match("Header",HeaderCells,0),Critieria_Range1,Criteria1,....)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable

    Sure, you can do that..

    =SUMIF(A1:A100,"your criteria",INDEX(A1:Z100,0,MATCH("Specific Title",A1:Z1,0)))

    Will find "your critaria" in column A, and sum corresonding values from the column that has "Specific Title" in Row 1.

    Hope that helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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