Countif error using multiple workbooks

[Melody4572]

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 F]

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

Dave

 

[Dave Peterson]

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.

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

 

[Melody4572]

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.

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.

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

 

[Sean Timmons]

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?

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.

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.

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

 

[Melody4572]

Yes both files are in the same folder, there is no error messages in the
formula bar. It shows the formula just find, the end resualts are Value#.
When I tried Sumproduct for the smae worksheet I get the smae error that I do
for the linked one. I wonder if Sumproduct doesn't like names?

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?

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.

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.


:

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

 

[Melody4572]

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?

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.

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]

You could use
=sum(if(...))
as an array formula.

But it should work the same way as =sumproduct()

If I were you, I'd get one portion of that long formula working. Then build on
that.

If you try just a single addend and still have trouble, post back the (smaller)
formula that you tried.

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?

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.

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

 

[Melody4572]

I tried the sumproduct for one month. it still isnt working.

=SUMPRODUCT(COUNTIF('C:\Documents and
Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January
aps'!$B$4:$B$400,$B22))

With both spreadsheets open the path is not in the formula, but with 2006
closed it will show the correct path (yes I am working on it from home and
not at work thus the reason for the C: blah blah instead S:blah blah.)

You could use
=sum(if(...))
as an array formula.

But it should work the same way as =sumproduct()

If I were you, I'd get one portion of that long formula working. Then build on
that.

If you try just a single addend and still have trouble, post back the (smaller)
formula that you tried.

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?

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]

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

Was my suggestion.

I didn't include =countif() and I used =$b5 near the end.

I'd build the formula with the workbook open and let excel worry about the
syntax when you close that "sending" workbook.

I tried the sumproduct for one month. it still isnt working.

=SUMPRODUCT(COUNTIF('C:\Documents and
Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January
aps'!$B$4:$B$400,$B22))

With both spreadsheets open the path is not in the formula, but with 2006
closed it will show the correct path (yes I am working on it from home and
not at work thus the reason for the C: blah blah instead S:blah blah.)

You could use
=sum(if(...))
as an array formula.

But it should work the same way as =sumproduct()

If I were you, I'd get one portion of that long formula working. Then build on
that.

If you try just a single addend and still have trouble, post back the (smaller)
formula that you tried.

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?

:

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

 

[Melody4572]

Life becomes bright and sunny once you slow down and read the fine print.
Sorry about that. I had misunderstood your original post. It works perfect
now. Thanks Dave!

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

Was my suggestion.

I didn't include =countif() and I used =$b5 near the end.

I'd build the formula with the workbook open and let excel worry about the
syntax when you close that "sending" workbook.

I tried the sumproduct for one month. it still isnt working.

=SUMPRODUCT(COUNTIF('C:\Documents and
Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January
aps'!$B$4:$B$400,$B22))

With both spreadsheets open the path is not in the formula, but with 2006
closed it will show the correct path (yes I am working on it from home and
not at work thus the reason for the C: blah blah instead S:blah blah.)

You could use
=sum(if(...))
as an array formula.

But it should work the same way as =sumproduct()

If I were you, I'd get one portion of that long formula working. Then build on
that.

If you try just a single addend and still have trouble, post back the (smaller)
formula that you tried.



Melody4572 wrote:

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?

:

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]

Glad you got it working!

Life becomes bright and sunny once you slow down and read the fine print.
Sorry about that. I had misunderstood your original post. It works perfect
now. Thanks Dave!

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

Was my suggestion.

I didn't include =countif() and I used =$b5 near the end.

I'd build the formula with the workbook open and let excel worry about the
syntax when you close that "sending" workbook.

I tried the sumproduct for one month. it still isnt working.

=SUMPRODUCT(COUNTIF('C:\Documents and
Settings\Owner\Desktop\Applicant\[Applicant Info 2006.xls]January
aps'!$B$4:$B$400,$B22))

With both spreadsheets open the path is not in the formula, but with 2006
closed it will show the correct path (yes I am working on it from home and
not at work thus the reason for the C: blah blah instead S:blah blah.)

:

You could use
=sum(if(...))
as an array formula.

But it should work the same way as =sumproduct()

If I were you, I'd get one portion of that long formula working. Then build on
that.

If you try just a single addend and still have trouble, post back the (smaller)
formula that you tried.



Melody4572 wrote:

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?

:

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