+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT failing with additional criteria of different range size

  1. #1
    Registered User
    Join Date
    06-23-2010
    Location
    Sylvan Lake, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Unhappy SUMPRODUCT failing with additional criteria of different range size

    Hello World,

    The formula below was doing a great job at calculating a weighted average, UNTIL an addition of criteria of different size.

    =IF(fldSupplierCost=0,fldWeightedPercent,IF(ISBLANK(fldSupplierID),(SUMPRODUCT(tblMar2019[Supplier Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0)))/(SUMPRODUCT(tblMar2019[Claim Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0))),(SUMPRODUCT(tblMar2019[Supplier Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0),--(tblMar2019[SAP]=fldSupplierID))/(SUMPRODUCT(tblMar2019[Claim Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0),--(tblMar2019[SAP]=fldSupplierID))))))

    The additional criteria is the several instances of

    --(tblMar2019[Product Line]=tblProductLine[Product Line])

    tblMar2019 is a table of say 20,000 records

    tblProductLine is a table that varies from 1 to 20 or so records

    With the additional criteria it throws a #N/A error. Tried array formula - still no go.

    Any suggestions would be oh so appreciated.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    3,853

    Re: SUMPRODUCT failing with additional criteria of different range size

    You need to multiply rather than using -- and separate arguments - e.g.

    SUMPRODUCT(tblMar2019[Supplier Cost]*(tblMar2019[Product Line]=tblProductLine[Product Line])*(tblMar2019[Supplier Cost]>0))
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,247

    Re: SUMPRODUCT failing with additional criteria of different range size

    you might find easiest to switch from:

    --(tblMar2019[Product Line]=tblProductLine[Product Line])

    to

    --ISNUMBER(MATCH(tblMar2019[Product Line],tblProductLine[Product Line],0))

    which would return an array relative to tblMar2019 thereby negating need to coerce any "numeric" arrays {reduces risk of #VALUE! errors etc}

    as a general pointer - when working with arrays of different dimensions you need to revert to a multiplication approach to standardise them [edit; per rorya], e.g.

    =SUMPRODUCT((A2:A10="x")*(B1:F1="y"),B2:F10)

    the multiplication of the criterion arrays ensures they mirror that of the summation range - i.e. 9x5, whereas the below would error (9x1, 1x5, 9x5)

    =SUMPRODUCT(--(A2:A10="x"),--(B1:F1="y"),B2:F10)
    Last edited by XLent; 05-22-2019 at 09:39 AM.

  4. #4
    Registered User
    Join Date
    06-23-2010
    Location
    Sylvan Lake, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: SUMPRODUCT failing with additional criteria of different range size

    Thank you roya and XLent!. Your tutelage is well received ... and solved the issue.

+ 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