SUMPRODUCT Question

[brans1982]

Hi,

Not sure is this can be done, maybe by using an array.

I'm doing a smiple sumproduct e.g.

=SUMPRODUCT((Data!A2:A1000=A1)*(Data!B2:B1000<>A2)*(Data!C2:C1000))

I have 10 values from A2 to A12 that I need in the above formula but instead of doing it like...

=SUMPRODUCT((Data!A2:A1000=A1)*(Data!B2:B1000<>A2)*(Data!B2:B1000<>A3)*(Data!B2:B1000<>A4)*(Data!B2:B1000<>A5)*(Data!B2:B1000<>A6)*(Data!B2:B1000<>A7)*(Data!B2:B1000<>A8)*(Data!C2:C1000))

Is it possible to have something like (Data!B2:B1000<>A2:A12)?

Thanks!

 

[Andrew Poulsom]

Try:

=SUMPRODUCT((Data!A2:A1000=A1)*ISERROR(MATCH(Data!B2:B1000,A2:A12,FALSE))*(Data!C2:C1000))

 

[brans1982]

Hi Andrew,

Thats perfect. Thanks for your help.

Conor.