+ Reply to Thread
Results 1 to 4 of 4

SUM a variable range with INDEX and MATCH

  1. #1
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    SUM a variable range with INDEX and MATCH

    The formula below works perfectly to find the number being matched but I need it to also calculate the SUM of a range with the last cell of that range being the matched cell, in this case TeamStats!F13, and the first cell of the range to be TeamStats!F5.

    =IF(C6="","",IF(ISERROR(INDEX(TeamStats!F:F,MATCH($E$1&C6,TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0))),"",INDEX(TeamStats!F:F,MATCH($E$1&C6,TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0))))

    Thanks!
    Last edited by BeachRock; 03-25-2012 at 07:03 PM.
    -------------
    Tony

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

    Re: SUM a variable range with INDEX and MATCH

    Hello Tony,

    With Excel 2010 you can use IFERROR rather than ISERROR to avoid repeating the formula....and I'd suggest using the same explicit range in column F (as columns A and B) rather than the whole column, so that would make your formula just

    =IF(C6="","",IFERROR(INDEX(TeamStats!F$1:F$1271,MATCH($E$1&C6, TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0)),""))

    Now if you want a SUM from TeamStats!F$5 to that cell change to

    =IF(C6="","",IFERROR(SUM(TeamStats!F$5:INDEX(TeamStats!F$1:F$1271,MATCH($E$1&C6, TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0))),""))
    Last edited by daddylonglegs; 03-25-2012 at 06:43 PM.
    Audere est facere

  3. #3
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    Re: SUM a variable range with INDEX and MATCH

    Hello and thanks for the reply!

    I tried the formula to "SUM from TeamStats!F$5 to that cell" and the result was a blank cell.

    If the matched index is TeamStats!F13, then the range to SUM should be TeamStats!F5:TeamStats!F13.

    Also, thanks for simplifying my formula! Much appreciated.

    **Just figured out why it wasn't working... I forgot to use Ctrl+Shift+Enter. Your formula works perfectly! Thanks very much daddylonglegs!
    Last edited by BeachRock; 03-25-2012 at 07:03 PM. Reason: forgot to use ctrl+shift+enter

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

    Re: SUM a variable range with INDEX and MATCH

    For an Excel 2003 compatible formula you shouldn't have to repeat the whole thing, assuming any error would be caused by MATCH you can just test that part, i.e.

    =IF(ISNA(MATCH($E$1&C6, TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0)),"",SUM(TeamStats!F$5:INDEX(TeamStats!F$1:F$1271,MATCH($E$1&C6, TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0))))

    ....or for any formula that returns a number you can convert errors to zero with this syntax

    =LOOKUP(9.99E+307,IF({1,0},0,formula))

    so with your formula that becomes

    =LOOKUP(9.99E+307,IF({1,0},0,SUM(TeamStats!F$5:INDEX(TeamStats!F$1:F$1271,MATCH($E$1&C6, TeamStats!B$1:B$1271&TeamStats!A$1:A$1271,0)))))

+ 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