Thanks:  0
Likes:  0

# Thread: COUNTIF problems... Has anyone else seen this?

1. 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?

2. 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.

3.

4. 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!

5. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•