Some functions won't work with closed workbooks. =countif() is one of them.

But you can replace each of your =countif()'s with =sumproduct().

=SUMPRODUCT(--('c:\[aaaa.xls]bbbb'!B2:B41="whatever you want here"))

could be a replacement.




Egon wrote:

I'm having a problem with Excel not updating references in a countif
formula to another workbook. Other spreadsheets are working fine, but
this one I have isn't updating properly.

Automatic calculation is turned on and even doing a force with F9
doesn't make any difference. The only way I can make it pull in the
values from the other workbook is if I open those other work books.
This isn't going to be a problem for another week, but being that they
are doing a calculation based on a workbook that is created daily its
going to be a problem having 30 of them opened at the end of the month.

Any suggestions on why this isn't work would be greatly appreciated.

J.

--

Dave Peterson