COUNTIF problems... Has anyone else seen this?
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com