CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Mr_Thomas
Mr_Thomas

Reputation: 869

Excel COUNTIF with link to another workbook

I am trying to get the count of records from a sheet in a different workbook. I'm using the following formula:

=COUNTIF('C:\Path\To\File\[WORK1.xlsm]SheetName'!$A:$A,"*")-1

NOTE: The file is an xlsM. It has a header row, that's why I'm subtracting 1

If I have the WORK1.XLSM file open at the same time as my 'Summary' workbook, then the numbers populate and are correct. Once I close WORK1, I get #VALUE in the cell.

So 2 questions:

  1. Is there a reason this won't work?

  2. Is there a better way to write this?

Thanks for your consideration.

Upvotes: 1

Views: 9734

Answers (1)

barry houdini
barry houdini

Reputation: 46451

COUNTIF doesn't work with closed workbooks, looks like you can use COUNTA, i.e.

=COUNTA('C:\Path\To\File\[WORK1.xlsm]SheetName'!$A:$A)-1

Upvotes: 2

Related Questions