LOL on the applicant spelling. Its been over a year and no one noticed it
until you pointed it out :)

As for the formula I did try that. I changed the formulas, rewrote the
formulas deleted the column readded a columns and started from scratch with
the product sum formula. Still not working correctly. I wonder if I turned
it into an array formula if it would work then?

"Dave Peterson" wrote:

Try something like this with the workbook open (easier to create the formula),
then close that workbook.

=sumproduct(--('[Aplicant Info 2006.xls]January aps'!$B$4:$B$400=$B5))
+more of the same....

ps. Applicant has two p's.





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

--

Dave Peterson