Combining several arrays into one formula

[ScorchedLemonade]

Hi,

I'm wanting to write a formula of the following type:

=sum(if(Sheet1!C1:C200&Sheet1!D1200=A1&B1,Sheet1!B1:B200*index(Sheet2!B1:B100,MATCH(Sheet1!A1:A200,Sheet2!A1:A100,0)),0))

or alternatively

=sumproduct(if(Sheet1!C1:C200&Sheet1!D1200=A1&B1,Sheet1!B1:B200,0),index(Sheet2!B1:B100,MATCH(Sheet1!A1:A200,Sheet2!A1:A100,0)),0))

but it looks like Excel refuses to run through the last of the 3 arrays of the same size (namely Sheet1!A1:A200), just taking the first one.

Is there a way I can get around this? I tried in the last of these arrays to & it with an array of the same size but filled with empty cells to try to force Excel into knowing there are 3 arrays involved here, but didn't see anything.

 

[ScorchedLemonade]

Not a problem anymore. It was figured out by a guy here at work. Thanks anyway to anyone who may have spent time on this. (Ultimately the solution involved writing a custom function to take care of part of the array problem.)