COUNTIF replacement for linked workbooks

  • Thread starter Thread starter Erich
  • Start date Start date
E

Erich

After several page flips I have come to the understanding
that Excel can not use the COUNTIF function for linked
workbooks when the source workbook is not open. I am
therefore in need of a suitable alternative.

I have a spreadsheet that exists on a shared server
(\\servername), in which there is a folder (dept). That
folder holds an excel workbook (file.xls) that is updated
by another department regularly.

What I would like to do is in another workbook, count how
many entries are in the external workbook. The data
stored in the cells is text, so I do not need to do any
mathematical operations on any of it aside from count how
many different entries there are in the first column.

The formula I was using was:
=COUNTIF('\\servername\dept\[file.xls]SheetName'!A:A,"<>0")

I get a #VALUE error when the source file is closed, but
it works beautifully when the source file is open. Can
anyone offer me a workable solution?

Thanks in advance for your help.

Erich
 
Hi
try
=SUMPRODUCT(--('\\servername\dept\[file.xls]SheetName'!A1:A1000<>0))

Note: SUMPRODUCT does not allow ranges like A:A
 
Thank you so much for that solution. I'm still trying to
figure out the syntax. What are the two dashes for?

Also, will this work using a URL instead of a server
address? Of course I'm going to start trying it now, but
if there's any special formatting I'll need to do, it
would be helpful to know that.

Thanks again.

Erich
-----Original Message-----
Hi
try
=SUMPRODUCT(--('\\servername\dept\[file.xls]SheetName'!
Click to expand...
A1:A1000 said:
Note: SUMPRODUCT does not allow ranges like A:A



--
Regards
Frank Kabel
Frankfurt, Germany

After several page flips I have come to the understanding
that Excel can not use the COUNTIF function for linked
workbooks when the source workbook is not open. I am
therefore in need of a suitable alternative.

I have a spreadsheet that exists on a shared server
(\\servername), in which there is a folder (dept). That
folder holds an excel workbook (file.xls) that is updated
by another department regularly.

What I would like to do is in another workbook, count how
many entries are in the external workbook. The data
stored in the cells is text, so I do not need to do any
mathematical operations on any of it aside from count how
many different entries there are in the first column.

The formula I was using was:
=COUNTIF('\\servername\dept\[file.xls]SheetName'!
Click to expand...
A:A said:
I get a #VALUE error when the source file is closed, but
it works beautifully when the source file is open. Can
anyone offer me a workable solution?

Thanks in advance for your help.

Erich
Click to expand...
.
Click to expand...
 
As I understand it, the argument to the right of the "--" returns a
TRUE or FALSE. The "--" convert that to a 1 or 0.

Erich said:
Thank you so much for that solution. I'm still trying to
figure out the syntax. What are the two dashes for?

Also, will this work using a URL instead of a server
address? Of course I'm going to start trying it now, but
if there's any special formatting I'll need to do, it
would be helpful to know that.

Thanks again.

Erich
-----Original Message-----
Hi
try
=SUMPRODUCT(--('\\servername\dept\[file.xls]SheetName'!
Click to expand...
A1:A1000 said:
Note: SUMPRODUCT does not allow ranges like A:A



--
Regards
Frank Kabel
Frankfurt, Germany

After several page flips I have come to the understanding
that Excel can not use the COUNTIF function for linked
workbooks when the source workbook is not open. I am
therefore in need of a suitable alternative.

I have a spreadsheet that exists on a shared server
(\\servername), in which there is a folder (dept). That
folder holds an excel workbook (file.xls) that is updated
by another department regularly.

What I would like to do is in another workbook, count how
many entries are in the external workbook. The data
stored in the cells is text, so I do not need to do any
mathematical operations on any of it aside from count how
many different entries there are in the first column.

The formula I was using was:
=COUNTIF('\\servername\dept\[file.xls]SheetName'!
Click to expand...
A:A said:
I get a #VALUE error when the source file is closed, but
it works beautifully when the source file is open. Can
anyone offer me a workable solution?

Thanks in advance for your help.

Erich
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top