SUMPRODUCT failing with additional criteria of different range size

1. 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. 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))

3. 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)

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)