+ Reply to Thread
Results 1 to 4 of 4

sumproduct can't evaluate array

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2013
    Posts
    6

    sumproduct can't evaluate array

    Hi,
    I am using this formula to match values in H with others in L, to extract corresponding values in M

    {=OFFSET($M$5;MATCH($H$7:$H$12;$L$6:$L$8;0);0;1;1)}

    Capture.PNG

    This works fine and the array formula evaluates like this
    {10;20;30;10;20;30}

    Now I would like to use this formula inside sumproduct,

    like this:

    {=SUMPRODUCT(OFFSET($M$5;MATCH($H$7:$H$12;$L$6:$L$8;0);0;1;1);$I$7:$I$12)}

    But it evaluates as 0??

    I hoped it would do the sumproduct of {1;2;3;4;5;6} and {10;20;30;10;20;30}

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

    Re: sumproduct can't evaluate array

    Hi,

    You need to coerce the OFFSET to numerical values with N() first:

    =SUMPRODUCT(N(OFFSET($M$5,MATCH($H$7:$H$12,$L$6:$L$8,0),0,1,1)),$I$7:$I$12)

    Regards
    Click * below if this answer helped

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

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: sumproduct can't evaluate array

    Hi XOR LX,

    Amazing you just made my day
    I was banging my head against the wall there for a while.

    Cheers

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

    Re: sumproduct can't evaluate 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. “Evaluate” a formula with = IF (OR (SUMPRODUCT(…. in VBA
    By FJY890 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2013, 11:51 PM
  2. Evaluate SUMPRODUCT limitations?
    By jamesryan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2010, 04:55 AM
  3. EVALUATE SUMPRODUCT formula
    By Robert in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2006, 03:35 AM
  4. Replies: 1
    Last Post: 03-08-2006, 04:00 PM
  5. Evaluate sumproduct question
    By ram in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-23-2005, 07:10 PM

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