SUMPRODUCT formula optimization for multiple criteria addition

Hello,

I have been trying to create a formula that is going to return needed structure with data changing. Since the data is in another workbook, i have resorted to use SUMPRODUCT, because SUMIF cannot read closed workbooks and that workbook will not always be open but i need the formula to still work. The problem is, i need to sum multiple criteria and SUMPRODUCT formula structure is pretty long in itself. This means i result with this monster of a formula:

=V157*(SUMPRODUCT(([IMPORT.xlsm]EU!$J$2:$J$10000="3212")*([IMPORT.xlsm]EU!$I$2:$I$10000))/(SUMPRODUCT(([IMPORT.xlsm]EU!$J$2:$J$10000="2441")*([IMPORT.xlsm]EU!$I$2:$I$10000))+SUMPRODUCT(([IMPORT.xlsm]EU!$J$2:$J$10000="3212")*([IMPORT.xlsm]EU!$I$2:$I$10000))+SUMPRODUCT(([IMPORT.xlsm]EU!$J$2:$J$10000="3213")*([IMPORT.xlsm]EU!$I$2:$I$10000))))

First lets strip down the links and other unimportant part of formula here. This is where the core is:

=SUMPRODUCT((J2:J10000="3212")*(I2:I10000))/(SUMPRODUCT((J2:J10000="2441")*(I2:I10000))+SUMPRODUCT((J2:J10000="3212")*(I2:I10000))+SUMPRODUCT((J2:J10000="3213")*(I2:I10000)))

What i would like to as is if there is any way to sum the values in column I based on multiple possible matches in column J. Basically i would like to see something like

SUMPRODUCT(J2:J10000="3212")*(I2:I10000)/SUMPRODUCT((SOMETHING HERE)*(I2:I10000)

That would give the same result.

I have tried using the OR function, but it only returns 0 in that case.

So.... something like this?

Formula:

Please Login or Register  to view this content.

Thank you, this has worked. It never ceases to amaze me how strangely SUMPRODUCT logic works. Its the complete opposite of everything else in excel. I guess thats what i get for working with IFs for a decade.