OK, my first question: If you click the = next to the formula bar, is the
error in only one, some, or all of the range references when you use
SUMPRODUCT?

Second, just to be sure, are both files in the same folder? If not, when the
2006 workbook is closed, does the SUMPRODUCT update to show the full path to
the 2006 file?

"Melody4572" wrote:

Yes, I know, but when I try the countproduct it gives me the same error also.
I was hoping someone else ran into this issue and have a work around or even
another formula that will work. :)

"Dave F" wrote:

COUNTIF doesn't work when it references an external workbook that is closed.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Melody4572" wrote:

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 :)