Aggregating Price using IFERROR/SUMIF-CodePudding

Problem Unable to sum prices for different part numbers using the combo of formula mentioned below

What Am I Trying To Accomplish? I am trying to sum up prices for various part numbers in a different sheet. They are in millions

My Formula I am using a combination of IFERROR and SUMIF

What my desired result should look like

Note When using the combo of IFERROR and SUMIF I am considering both Part num and ALT Part so that if either of the two exists, it should sum the price for both

Thanks

CodePudding user response：

If I’m understanding this right, the formula you are probably looking for is =iferror(sumif1,0) iferror(sumif2),0)

CodePudding user response：

Perhaps you may try using SUMPRODUCT() Function,

• Formula used in cell C2

=SUMPRODUCT((--ISNUMBER(MATCH($G$2:$G$9,A2:B2,0)))*($F$2:$F$9))

To explain the above Formula.

• We are using MATCH() Function to find the positions of the both Part Numbers in the range G2:G9

=MATCH($G$2:$G$9,A2:B2,0)

The above on evaluating returns

{1;#N/A;#N/A;#N/A;2;#N/A;#N/A;#N/A}

• Next we are wrapping this within an ISNUMBER() Function to exclude the #N/A and to take only numbers.

ISNUMBER(MATCH($G$2:$G$9,A2:B2,0))

Which returns

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

• Using double unary to convert the Boolean values to 1's and 0's

{1;0;0;0;1;0;0;0}

• Next we are then doing a matrix multiplication of the 1's with the corresponding range i.e. F2:F9

(--ISNUMBER(MATCH($G$2:$G$9,A2:B2,0)))*($F$2:$F$9)

Which returns,

{5000;0;0;0;5000;0;0;0}

• Lastly we wrapping the whole within an SUMPRODUCT() Function to get the SUM

=SUMPRODUCT((--ISNUMBER(MATCH($G$2:$G$9,A2:B2,0)))*($F$2:$F$9))

Hence we are not using and IFERROR() here since IFERROR() function is relatively inefficient and makes calculations slow, if there was no option then we could have used it perhaps when there is way why not use the formula as shown in the screenshot.

Also note to change the ranges in the formulas as per your suit.