I have a countif formula that I am using to keep track of how many times
someone comes in to apply. It is set up per month per sheet. To keep track
of multiple applicants I have a column that shows if they have been here
before. We have had several people from last year come back so now I need to
link it to last years information. As long as the workbooks are both open,
no problem. Once 2006 is closed the column that holds this information all
turns into VALUE# errors. I have tried the SUMPRODUCT and it immediately
errs out to again the VALUE#. The formula that works is:

=COUNTIF('[Aplicant Info 2006.xls]January
aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]February
Aps'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]March
Aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]April
aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]May
aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info
2006.xls]June'!$B$4:$B$399,$B5))+(COUNTIF('[Aplicant Info 2006.xls]July
aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]August
aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]September
aps'!$B$4:$B$400,$B5))+(COUNTIF('[Aplicant Info 2006.xls]October
aps'!$B$4:$B$398,$B5))+(COUNTIF('[Aplicant Info 2006.xls]November
aps'!$B$4:$B$400,$B5)+(COUNTIF('[Aplicant Info 2006.xls]December
aps'!$B$4:$B$400,$B5)))

But I truly would like it to work without opening the other workbook. I
know what a baby :)