COUNTIF problems... Has anyone else seen this?

Wingz · Apr 11th, 2002, 11:57 AM

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 · Apr 11th, 2002, 12:43 PM

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. · Apr 11th, 2002, 01:49 PM

See http://support.microsoft.com/default...;en-us;Q237188.

Wingz · Apr 11th, 2002, 03:06 PM

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. · Apr 11th, 2002, 03:09 PM

Quote:

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 11:57 AM	#1
Wingz New Member Join Date: Mar 2002 Posts: 33	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?

Apr 11th, 2002, 12:43 PM	#2
Wingz New Member Join Date: Mar 2002 Posts: 33	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!

Apr 11th, 2002, 01:49 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See http://support.microsoft.com/default...;en-us;Q237188.

Apr 11th, 2002, 03:06 PM	#4
Wingz New Member Join Date: Mar 2002 Posts: 33	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!