How to write SUMPRODUCT where one array is (A1:A10 = B1:B1000)

Question

I am trying to write a SUMPRODUCT formula which sums values based on multiple criteria. One criteria is matching a list of ten codes to a thousand codes associated with the values I want summed.

My simplified equation is this:

=SUMPRODUCT(--($C3=$D$1:$D$1000), --($A$1:$A$10=$B$1:$B$1000), ('Cons and EP FY16'!$H$1:$H$1000))

where C and D are names, A and B are the codes I want to match, and H is the value to be summed.

The above equation returns an error. However, if you replace the $A$1:$A$10 with a single code, say 99203, it works.

I would appreciate any help you can lend me.

Thanks!

Answer 1

Use MATCH:

=SUMPRODUCT(($C3=$D$1:$D$1000)*(ISNUMBER(MATCH($B$1:$B$1000,$A$1:$A$10,0))), ('Cons and EP FY16'!$H$1:$H$1000))