sumproduct error

[simonf]

Can someone tell me what's wrong with this formula? I kept on getting #N/A.

=SUMPRODUCT(('[FY03 Budget.xls]Summary'!$L$37:$L$64=B65)*('[FY03 Budget.xls]Summary'!$M$9:$X$9=C4)*'[FY03 Budget.xls]Summary'!$M$37:$Y$64)

$L$37:$L$64 are the departments
$M$9:$X$9 are the months
$M$37:$Y$64 are the budget numbers

They are all on a differnt workbook.

Thanks in advance.

 

[Jay Petrulis]

Hi,

SUMPRODUCT requires that the arrays you are using be identically sized. One can be a row, another a column, but the number of elements must be equal. All the ranges are different in your formula.

You also have a multiple row/column range being tested, and that will require another approach, I believe.

 

[Aladin Akyurek]

On 2002-10-11 15:41, simonf wrote:
Can someone tell me what's wrong with this formula? I kept on getting #N/A.

=SUMPRODUCT(('[FY03 Budget.xls]Summary'!$L$37:$L$64=B65)*('[FY03 Budget.xls]Summary'!$M$9:$X$9=C4)*'[FY03 Budget.xls]Summary'!$M$37:$Y$64)

$L$37:$L$64 are the departments
$M$9:$X$9 are the months
$M$37:$Y$64 are the budget numbers

They are all on a differnt workbook.

Thanks in advance.

$Y$64 should probably be $X$64. Right?

 

[simonf]

Just figured it out by using INDEX(range, MATCH, MATCH). Thanks for all the helps.

 

[Aladin Akyurek]

On 2002-10-11 16:22, simonf wrote:
Just figured it out by using INDEX(range, MATCH, MATCH). Thanks for all the helps.

I'd like to see an answer to the question I posed. You might get a better formula.