+ Reply to Thread
Results 1 to 5 of 5

Help with SUMPRODUCT and Match using a Range of values for Lookup

  1. #1
    Registered User
    Join Date
    01-21-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    2

    Help with SUMPRODUCT and Match using a Range of values for Lookup

    Hi all,

    I've been searching the internet for a few days and cannot seem to find the solution to my problem. I can usually figure out/ find on the internet solutions but I'm stumped as to why the following equation won't work: {=SUMPRODUCT((Data!$C$2:$C$13189),(--(Data!$B$2:$B$13189=$B$2)*(--(MATCH(Output!$A$2:$A$26,Data!$A$2:$A$13189,0)))))}

    I need to be able to get the Total for the selection. The Data!$C$2:$C$13189 is the dollars I need and Data!$B$2:$B$13189=$B$2 is my year criteria which both work fine. When I add in the Office criteria, I get #N/A. I'm trying to allow the user to select multiple offices and I need the sumproduct to look up every office put into the Output!$A$2:$A$26 range in the Data!$A$2:$A$13189 range.

    Any help will be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

    Hi,

    You have a couple of issues with your formula.

    Firstly, the MATCH lookup_value and lookup_array are the wrong way round.

    Secondly, you need to coerce the #N/As from this MATCH into Booleans using e.g. ISNUMBER.

    Try:

    =SUMPRODUCT((Data!$C$2:$C$13189),(--(Data!$B$2:$B$13189=$B$2)*(--(ISNUMBER(MATCH(Data!$A$2:$A$13189,Output!$A$2:$A$26,0))))))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

    Oh, and by the way, not sure why you've got those curly brackets around the formula in your post as this does not need to be array-entered.

    Regards

  4. #4
    Registered User
    Join Date
    01-21-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

    Thank you thank you thank you!!!!!!

    I don't normally use MATCH so I could see how I got it the wrong way around. Again, thank you for your help I really appreciate it

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

    You're most 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. [SOLVED] problem with SUMPRODUCT looking to match a date range
    By jshaw82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 03:42 PM
  2. [SOLVED] Sumproduct to match values in different cells
    By raj.bris in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-08-2013, 09:39 PM
  3. [SOLVED] Range lookup for x and y - sumproduct? index?
    By outatime1.21 in forum Excel General
    Replies: 5
    Last Post: 06-07-2012, 06:41 PM
  4. Replies: 1
    Last Post: 06-28-2010, 09:43 PM
  5. Replies: 2
    Last Post: 04-01-2010, 11:19 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