+ 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
    Cadiz, KY
    MS-Off Ver
    2016
    Posts
    6

    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
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    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

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

    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
    Cadiz, KY
    MS-Off Ver
    2016
    Posts
    6

    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)

Similar Threads

  1. [SOLVED] Sumifs again....with hard coded number range and additional criteria
    By TreeLife in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-09-2018, 05:00 PM
  2. [SOLVED] Lookup a value within a range with 2 additional criteria, return a value
    By gaker10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2018, 08:17 PM
  3. Replies: 14
    Last Post: 10-03-2017, 10:39 AM
  4. [SOLVED] Sumif between a range of dates plus one additional criteria
    By caliskier in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2014, 06:03 PM
  5. Count a single range using additional criteria from another range
    By FatFoot in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-27-2013, 08:40 PM
  6. Additional criteria for 'sumproduct' formula
    By dallston in forum Excel General
    Replies: 7
    Last Post: 01-28-2009, 02:18 PM
  7. sumproduct additional information for adding another criteria
    By Darlo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2007, 11:30 AM

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