+ Reply to Thread
Results 1 to 8 of 8

sumif, index, match + SUM MULTIPLE columns

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    sumif, index, match + SUM MULTIPLE columns

    Hello,

    I have the a formula below that I'm trying to 'dynamically' sum the column of the months based on a criteria.

    Please Login or Register  to view this content.
    I checked via the evaluate formula, and I can see that my 'sum_range' is taking from the column that I want, i.e. result is Sheet1!$S:$Z.

    But it's giving me a #VALUE! error.

    So what is wrong in my above formula?

    If I'm not using the correct formula, what would be the correct formula that sums up multiple columns (i.e. from Jan to Aug, or Apr to Jun etc) given a criteria(s)

    Thanks!

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

    Re: sumif, index, match + SUM MULTIPLE columns

    I checked via the evaluate formula, and I can see that my 'sum_range' is taking from the column that I want, i.e. result is Sheet1!$S:$Z.
    So then the sumif becomes
    =SUMIFS(Sheet1!$S:$Z,Sheet1!$B:$B,$E$1)

    Sumifs cannot do that. The sum range must be of similar dimension to the Criteria range.
    You would need sumproduct

    =SUMPRODUCT((Sheet1!$B:$B=$E$1)*Sheet1!$S:$Z)

    However, I HIGHLY recommend NOT using entire column references like B:B in sumproduct.
    Restrict it to the actual used area
    =SUMPRODUCT((Sheet1!$B$2:$B$1000=$E$1)*Sheet1!$S$2:$Z$1000)

  3. #3
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: sumif, index, match + SUM MULTIPLE columns

    Thanks Jonmo!

    Due to the range of rows that can increase as time passes, that's the tendency that I'm using the whole column.

    I tried to use the SUMPRODUCT formula and was still giving me the #VALUE! error.

    Please Login or Register  to view this content.
    I am just wondering if

    Please Login or Register  to view this content.
    is giving the error

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

    Re: sumif, index, match + SUM MULTIPLE columns

    You shouldn't need the extra () around INDEX:INDEX
    Try

    =SUMPRODUCT(INDEX(Sheet1!$A:$AE,0,MATCH(C35,Sheet1!$1:$1,0)+MATCH("JAN 2016",Sheet1!$F$2:$R$2,0)-1):INDEX(Sheet1!$A:$AE,0,MATCH(C35,Sheet1!$1:$1,0)+MATCH("AUG 2016",Sheet1!$F$2:$R$2,0)-1)*(Sheet1!$B:$B=$E$1))

    I undrestand the need to accomodate fluctuating ranges.
    But I think in this case, the 'very significant' performance hit that entire column references will cause in this formula far outweigh that need.
    Can you come up with a number that represents the largest # of records you can reasonably say it would never exceed ?
    Use that # + 10%

    This is also an issue with this formula because TEXT strings within the Iindex:Index range will cause errors with the math.
    So I would guess you likely have Header Strings in Row 1, so that alone requires you to not use entire columns.

  5. #5
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: sumif, index, match + SUM MULTIPLE columns

    I tried again, and still to no avail.

    I've attached a sample

    I believe something is wrong with my 2 INDEXs formula.

    Please Login or Register  to view this content.
    Reason being, now when I checked it, it becomes "Sheet1$S$3:$S$100 : Sheet1$Z$3:$Z$100" as an example. Instead of "Sheet1$S$3:$Z$100"
    Attached Files Attached Files
    Last edited by dluhut; 08-08-2016 at 04:57 PM. Reason: Incorrect sample file

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

    Re: sumif, index, match + SUM MULTIPLE columns

    Your 2 index functions are each returning a multicell range
    Like A1:A100 and F1:F100 for example..

    Combining them like INDEX:INDEX ends up making a range like
    A1:A100:F1:F100

    That's not a valid range reference.
    You want the first index to return a SINGLE cell in row 3
    and the 2nd index to return a single cell in row 107


    You also had a misplaced paren
    You had
    sumproduct(Index(match()):index(match()))*(range=x)
    should be
    sumproduct(Index(match()):index(match())*(range=x))


    Try
    =SUMPRODUCT(INDEX(Sheet1!$A$3:$AE$3,MATCH(C3,Sheet1!$1:$1,0)+MATCH("JAN 2016",Sheet1!$F$2:$R$2,0)-1):INDEX(Sheet1!$A$107:$AE$107,MATCH(C3,Sheet1!$1:$1,0)+MATCH("AUG 2016",Sheet1!$F$2:$R$2,0)-1)*(Sheet1!$B$3:$B$107=Summary!$E$1))

    Now this still returns 0 in your example book, because there are no cells in Sheet1!$B$3:$B$107 that are equal to the value in Summary!$E$1.

  7. #7
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: sumif, index, match + SUM MULTIPLE columns

    Solved!

    Thanks Jonmo1...and reps up to you!

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

    Re: sumif, index, match + SUM MULTIPLE columns

    You're welcome.

+ 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. INDEX MATCH MATCH multiple columns with same heading
    By djm198 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2016, 02:34 PM
  2. VBA code for combined sumif index/match for multiple criteria
    By megaheinz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-16-2016, 11:44 AM
  3. Sumif Index match with multiple column same criteria
    By lilym9053 in forum Excel General
    Replies: 3
    Last Post: 01-08-2016, 12:38 PM
  4. Sumif or Index/Match on Multiple Criteria including Dates to Month?
    By patrick1024 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2015, 11:00 PM
  5. [SOLVED] index match and sumproduct or sumif on multiple criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 06:20 AM
  6. [SOLVED] need help on Sumif with index and match multiple criteria
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-11-2013, 12:53 AM
  7. Replies: 2
    Last Post: 01-27-2012, 01:32 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