Array returns error message #Num

[jthompson]

=SUM(IF(A2='[OCR Blue.xls]OCR Contract r20'!$D:$D,'[OCR Blue.xls]OCR Contract r20'!$AA:$AA))

I am tyring to look for the item in A2 and go to another sheet and look for that value in column "D" and then sum the items in the corresponding column"AA:AA". I am getting the error message #NUM. Any help would be appreciated.

 

[Juan Pablo González]

Array formulas can't look at entire columns (AA:AA, D:D), you have to define a finite range.

However, looks like you could use a simple SUMIF (which can work on the whole column)

 

[jthompson]

Thanks! That worked great. I think I was tyring to make that a lot harder than it was.