COUNTIF problems... Has anyone else seen this?

[Wingz]

I am making reference to a range in another workbook(workbbok is closed at the time) and performing a COUNTIF formula. I get a #Value error UNTIL I open up the file. As soon as the file being referenced gets open, the formula works perfectly. When I reference that file NOT using a COUNTIF statement, it works fine as well so I know the reference syntax is correct. The funny thing is, this COUNTIF statement worked fine yesterday and now today it does not. I don't know if I changed something that would casue this. Any ideas?

 

[Wingz]

Hmmm... I just went and tried a formula on a co-worker's machine and I got the same thing. The strange thing is, when I use the SUM formula, it works fine. The problem appears to be the COUNTIF formula itself. Anyone else want to give it a try?
Make an excel file called test.xls and put in the numbers 1 through 10 in a1:a10. Then save and close that file as c:My Documentstest.xls. Open up a new file and in a1 type type in the formula =countif('c:My Documents[test.xls]Sheet1'!a1:a10,1)
* This should give you a #Value.
If you open up the test.xls file, it will give you the correct answer(which is 1 in this case)
Alternatively, you can leave the test.xls file closed and change the formula to read =sum('c:My Documents[test.xls]Sheet1'!a1:a10) This will also produce the correct answer.
This has me stumped.... Please help!

 

[Mark W.]

See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q237188.

 

[Wingz]

Mark,

Thank you so much. I though I was losing my mind! Makes me feel a little bit better to know that I had the logic right and it is an Excel flaw. Whew... I was about to throw in the towel!

 

[Mark W.]

On 2002-04-11 14:06, Wingz wrote:
Mark,

Thank you so much. I though I was losing my mind! Makes me feel a little bit better to know that I had the logic right and it is an Excel flaw. Whew... I was about to throw in the towel!

I wouldn't call it an "Excel flaw". It's just a limitation of COUNTIF, SUMIF, etc. Simply use an equivalent array formula as suggested by the article.