Biff,

Perfect! But could you explain what is happening, because I'm gonna need to
use it again with SUMIF and such.

Thanks.



"T. Valko" wrote:

COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.

Have the source file open when you write the formula and then just use your
mouse to point to the source. This way Excel will put all that path junk in
for you. Much easier than typing it!

=SUMPRODUCT(--(really_long_pathC1:C100=E5))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
I have a workbook with a formulas referencing 5 other workbooks. Whenever
I
sort the results, or close and open it, all results turn into #Value. The
numbers come back when I reopen the workbook being referenced, but really,
there must be something we can do?

Here's one of those formulae:
=COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma
2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking
underlines everything, then just E5...so there.

Also, is there a way to bypass the Update Links screen if the file hasn't
been moved or renamed?

I thank you for your time.

Arlen