MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sumif function


Posted by Jim Sheldon on March 06, 2001 10:09 AM

I am using the sumif function to read values out of another workbook
that resides on our companies server. The function only seems to work
correctly when the referenced file is open. I thought that function
should work without the file being opened. Any ideas?


Posted by Dave Hawley on March 06, 2001 10:14 AM


Hi Jim

It should still work if the file is not open. What do you mean by "only seems to work
correctly" ?
Is the referenced file password protected or Read Only ?

Dave

OzGrid Business Applications

Posted by Jim Sheldon on March 06, 2001 10:44 AM

When the criteria field is a constant and the referenced file is open, the function works.
When the criteria field is a constant and referenced file is not open, the function will
retain the last saved value. If you try to recalculate, while the referenced file is
closed, "# Value" is the value I will get. The file is not password protectected nor
read-only.


Jim

Posted by Dave Hawley on March 06, 2001 11:09 AM

Looks like SUMIF needs the file to be open. You can use an array formula to get the same result though:
=SUM(IF('C:\[MecureUserform.xls]RECONCILATION'!$C$5:$C$15>1,'C:\[MecureUserform.xls]RECONCILATION'!$C$5:$C$15))


and enter with Ctr+Shift+Enter


Dave
OzGrid Business Applications