CountIF function referencing external workbook

[ScubaNut]

I am running into a problem using the COUNTIF function when the range parameter is in another workbook. First some setup info:
Excel 2003 - 2 workbooks open as follows:

TestA.xls has a single worksheet with the following values in column A:
1, 2, 3, 4, 3, 3 (A1 to A6)

TestB.xls has the following two formulas in 2 different cells:
=COUNTA([TestA.xls]Sheet1!$A$1:$A$6) (Value = 6)
=COUNTIF([TestA.xls]Sheet1!$A$1:$A$6,"3") (Value = 3)

No problem so far. When I now close TestA.xls (my data source workbook), my formulas in TestB.xls (correctly) change to:
=COUNTA('C:\TEMP\[TestA.xls]Sheet1'!$A$1:$A$6)
=COUNTIF('C:\TEMP\[TestA.xls]Sheet1'!$A$1:$A$6,"3")

BUT after forcing the values to update in TestB.xls (either use Edit - Links - Update Values OR close/re-open the file) the results show as:
6 (same/correct value when TestB was open)
#VALUE! (?? for the COUNTIF function)

To me it seems almost as if the COUNTIF function doesn't like references to closed workbooks (even though the Help indicates otherwise). I can go and re-open TestA.xls, update it, close it and my first formula above does correctly get updated (telling me the link IS working). My second formula remains #VALUE!
Searching here & Google yeilded nothing as of yet. My first post here, so I hope I've added sufficient detail (without breaking too many rules).

 

[barry houdini]

Hello Denis, Welcome to Mr Excel

Your assumption is correct, COUNTIF won't work with closed workbooks, try changing to SUMPRODUCT, i.e.

=SUMPRODUCT(--([TestA.xls]Sheet1!$A$1:$A$6=3))

edit: see Microsoft's write up here which I believe applies to all Excel versions not just 1997 (but don't know about 2007). Note suggested replacement is a SUM(IF type formula but SUMPRODUCT will work just as well.....

 

[ScubaNut]

Thanks for COUNTIF bug info.

Thank you for such a quick & accurate response (so much for my MSDN search skills & tools).
Yes, the ?BUG? does affect my version. For everyone else's benefit, see here for MS's latest (Feb '07) article on this.

Thanks again!

Hello Denis, Welcome to Mr Excel

Your assumption is correct, COUNTIF won't work with closed workbooks, try changing to SUMPRODUCT, i.e.

=SUMPRODUCT(--([TestA.xls]Sheet1!$A$1:$A$6=3))

edit: see Microsoft's write up here which I believe applies to all Excel versions not just 1997 (but don't know about 2007). Note suggested replacement is a SUM(IF type formula but SUMPRODUCT will work just as well.....