COUNTIF error when linking another workbook

[cingraha]

Hello, I have a question that hopefully has a simple answer. I am trying to do a countif in another workbook. I enter my formula:
=COUNTIF('path[filename]tab'!$AH$11:$AH$300,"yes")

So this formula works fine when I have the other workbook open, but the next day when I open up the file (with the formula in it) and I click "update" button on the "this workbook contains links to other data sources" popup I get a #VALUE! error in the cell. Does anyone know how to correct this?

As soon as I open up the linked workbook the error goes away, but I didn't think this is how its supposed to function. I thought you didn't have to open up any linked documents. All the documents are on my desktop, so its not a share drive issue.

To test it out, I tried doing just a simple link to cell A1 in the other workbook and that works fine, so I'm thinking its the COUNTIF function that causing the problem.

Thanks for any help!

 

[Mark W.]

...So this formula works fine when I have the other workbook open, but the next day when I open up the file (with the formula in it) and I click "update" button on the "this workbook contains links to other data sources" popup I get a #VALUE! error in the cell. Does anyone know how to correct this?

COUNTIF and SUMIF cannot reference a closed workbook (see http://support.microsoft.com/default.aspx?scid=kb;en-us;237188&Product=xlw97). If you need this functionality use...

{=COUNT(IF('path[filename]tab'!$AH$11:$AH$300="yes",1))}

 

[cingraha]

Works perfect!

Thanks for the solutions!

 

[dayxday8]

COUNTIF and SUMIF cannot reference a closed workbook (see http://support.microsoft.com/default.aspx?scid=kb;en-us;237188&Product=xlw97). If you need this functionality use...

{=COUNT(IF('path[filename]tab'!$AH$11:$AH$300="yes",1))}

Hello - I just tried this and was successful but i want to be able to use a "contains *" so instead of ...="yes" it would read ="8*" this works when taking from a sheet on the same workbook so i know it works, it must have to do with the linking... because if i use ...="8-1" instead it is perfect but i have many ranges and dont want to have type them all out. Any help would be appreciated.

 

[Aladin Akyurek]

Hello - I just tried this and was successful but i want to be able to use a "contains *" so instead of ...="yes" it would read ="8*" this works when taking from a sheet on the same workbook so i know it works, it must have to do with the linking... because if i use ...="8-1" instead it is perfect but i have many ranges and dont want to have type them all out. Any help would be appreciated.

What did you exacly try?

 

[dayxday8]

i tried:

{=COUNT(IF('path[filename]tab'!$F$6:$F$57="9*",1))}

{=COUNT(IF('path[filename]tab'!$F$6:$F$57="9-1",1))} is what works...

 

[Aladin Akyurek]

i tried:

{=COUNT(IF('path[filename]tab'!$F$6:$F$57="9*",1))}

{=COUNT(IF('path[filename]tab'!$F$6:$F$57="9-1",1))} is what works...

One of:

Control+shift+enter, not just enter...

=COUNT(IF(LEFT($F$6:$F$57)="9",1))

=SUM(IF(LEFT($F$6:$F$57)="9",1))

Adjust to suit by adding the path bit.

 

[dayxday8]

are you saying to type it as:

{=COUNT(IF(LEFT('path[filename]tab'!$F$6:$F$57="9",1)))}

It says the formula has an error when I do this (I am hitting CSE)

 

[Aladin Akyurek]

are you saying to type it as:

{=COUNT(IF(LEFT('path[filename]tab'!$F$6:$F$57="9",1)))}

It says the formula has an error when I do this (I am hitting CSE)

=COUNT(IF(LEFT('path[filename]tab'!$F$6:$F$57)="9",1))

 

[dayxday8]

right thats what i have (the {} just says that i'm hitting CSE)

then i get the "The formula you typed contans an error."