+ Reply to Thread
Results 1 to 6 of 6

sum, index, match, for multiple look up array

  1. #1
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    48

    sum, index, match, for multiple look up array

    Hi all,

    I have been using a standard Index,Match,Match formula, works great, however the formula can get very long. I am looking for a way to shorten it, by selecting the array of values I want to be retrieved.

    In the spreadsheet example: Cell N7 produces the desired result, but can lead to novel style formulas if I have 30+ records I am trying to sum

    Any ideas as to how I can shorten the formula, preferably to be able to select the range M8:M12

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: sum, index, match, for multiple look up array

    Here is a better formula to sum range of cells for revenue
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you want to reference N6 cell use formula below
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 09-05-2016 at 08:09 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: sum, index, match, for multiple look up array

    Hi AlKey, Thanks for the quick response.

    Something I should have mentioned, cell N6 would be a combo Box/ In List selection, so I would need to Index all columns, right now cell N6 is referencing Revenue, however any change to the reference would need to sum a column other than D:D.

    Thanks,
    Dean

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,351

    Re: sum, index, match, for multiple look up array

    Or try this ...

    =SUMPRODUCT(--(COUNTIF($M$8:$M$12,$C$6:$C$17)>0),OFFSET($C$6:$C$17,,MATCH(N6,$D$5:$J$5,0)))

  5. #5
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: sum, index, match, for multiple look up array

    Thanks both, Phuocam, this works wonderfully. thank you!

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,351

    Re: sum, index, match, for multiple look up array

    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. Sum of multiple Index/Match results using array formula
    By cray36 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-25-2015, 11:12 PM
  2. Lookup OR Index/Match with multiple Array's
    By Numnum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2014, 05:32 AM
  3. Index Match Array Multiple Criteria
    By Keelin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-23-2014, 02:48 AM
  4. LOOKUP with Multiple Criteria (ARRAY INDEX and MATCH)
    By snowktt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2014, 02:24 AM
  5. Array formula - index and match with multiple statements
    By A[L]C in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-06-2013, 11:13 AM
  6. [SOLVED] Index and Match Function across multiple array
    By Ray Park in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-10-2012, 07:07 PM
  7. IF then or index match w/ arguements for multiple mins in an array
    By lenick in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2012, 10:54 AM

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