SUMIF for closed workbooks returning #VALUE! error

[zoefrannie]

I am trying to use the formula below but am getting #VALUE! errors. Ranges A:A and B:B exists in a closed workbook.

SumIF('C:\Closed Folder\[Closed Excel File.xls]Sheet1'!A:A,">="&C1",'C:\Closed Folder\[Closed Excel File.xls]Sheet1'!B:B)

 

[Domenic]

Use SUMPRODUCT instead...

=SUMPRODUCT(--('C:\Closed Folder\[Closed Excel File.xls]Sheet1'!$A$2:$A$100>=C1),'C:\Closed Folder\[Closed Excel File.xls]Sheet1'!$B$2:$B$100)

Adjust the ranges accordingly. Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

 

[zoefrannie]

Thanks, Domenic. Works great!