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!

 

[Aladin Akyurek]

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

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

You had a misplaced paren...

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

 

[LearnMeExcel]

Hi
i think Sumproduct work perfectly with clos workbook

 

[efarley]

This was very helpful when creating a count blanks formula which references another file. Can you give me any suggestions regarding how to modify this to count the number of records in a range where the date is <Today()-30. I used the following: =COUNT(IF('path[filename]tab'!$AH$11:$AH<Today()-30,1)), and it works, but it is not returning the correct value. It should return 12, but it is returning 24. I thought it might be including blanks, but we have a total of 14 blanks in the range, which would be a total of 26, not 24.

 

[Aladin Akyurek]

This was very helpful when creating a count blanks formula which references another file. Can you give me any suggestions regarding how to modify this to count the number of records in a range where the date is <today()-30. 24.="" not="" 26,="" of="" total="" a="" be="" would="" which="" range,="" the="" in="" blanks="" 14="" have="" we="" but="" blanks,="" including="" might="" it="" thought="" i="" returning="" is="" 12,="" return="" should="" value.="" correct="" works,="" and="" used="" quote]

</today()-30.>

 

[Aladin Akyurek]

This was very helpful when creating a count blanks formula which references another file. Can you give me any suggestions regarding how to modify this to count the number of records in a range where the date is <today()-30. 24.="" not="" 26,="" of="" total="" a="" be="" would="" which="" range,="" the="" in="" blanks="" 14="" have="" we="" but="" blanks,="" including="" might="" it="" thought="" i="" returning="" is="" 12,="" return="" should="" value.="" correct="" works,="" and="" used="" quote]

</today()-30.>

Care to elaborate on what you exactly want to count?

 

[efarley]

I think part of my post cut off. I am trying to count the number of dates in a range that are older than today()-30, or greater than a month old. I used this formula =COUNT(IF('path[filename]tab'!$F$6:$F$57<Today()-30,1)), and it worked, but it is not returning the correct count. In my range I have 12 dates older than 30 days, but it is returning a count of 24. I thought it might be including blanks in the range, but the number of blank cells in the range is 14, which with the 12 actual dates older than 30 days, would result in a count of 26.

 

[Aladin Akyurek]

I think part of my post cut off. I am trying to count the number of dates in a range that are older than today()-30, or greater than a month old. I used this formula =COUNT(IF('path[filename]tab'!$F$6:$F$57<today()-30,1)), 12="" 24.="" not="" of="" a="" be="" would="" which="" range,="" the="" in="" blanks="" have="" but="" including="" might="" it="" thought="" i="" returning="" is="" correct="" and="" quote]


Care to state the ranges one by one and what must hold for those ranges for counting to occur?
</today()-30,1)),>

 

[efarley]

I realized from looking at your FAQ that my post was being truncated due to the inclusion of a Less Than sign. I will make one more attempt to explain what I need to do. I have several workbooks, which are identically composed, that contain a number of equipment items with a column for last review date, this is a date formatted field. This information is all maintained in separate files because there are multiple groups maintaining the different types of equipment. I have another workbook, in which I need to summarize the data in all of these external workbooks. I modified your formula to count blanks, and it worked very well. I tried to modify it to count the number of items in each workbook with a date that is greater than 30 days old, I will include the formula I used in the last line of the post to ensure my post is not truncated again. The result it returns is not accurate. In my data, I count 12 records with dates older than 30 days, but the formula returns a result of 24. I thought it might be counting blanks, but there are 14 blanks, which when added to the 12 older dates would total 26. I also thought it might be doubling the count for some reason, so I divided the result by 2, but when I added another record with an older date, it returned a fraction result, so it doesn't appear to be doubling. Is there something else I need to do in my formula to return the correct result? One additional note, I don't know if this matters, but I am creating this formula in a macro enabled workbook. Using Office 2007.

As suggested in FAQ, I added spaces before and after the Less Than sign in the following example of my formula.

=COUNT(IF(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99 < TODAY()-30,1))

Thank you for any assistance, and I apologize for the 2 truncated posts.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=COUNT(IF(ISNUMBER(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99),IF(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99 < TODAY()-30,1)))

Equally (perhaps better)...

=SUM(IF(ISNUMBER(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99),IF(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99 < TODAY()-30,1)))

 

[efarley]

Control+shift+enter, not just enter:

=COUNT(IF(ISNUMBER(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99),IF(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99 < TODAY()-30,1)))

Equally (perhaps better)...

=SUM(IF(ISNUMBER(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99),IF(path[filename.xlsm]Asset Health Report'!$Q$16:$Q$99 < TODAY()-30,1)))

Worked perfectly...thank you.