COUNTIF() w/ External Reference

[darkke]

I am trying to reference an outside workbook with the COUNTIF() function
and keep getting #VALUE errors.

Here is an example of what I am trying to do:


Code:
--------------------

=COUNTIF('\\server1\share\PFA_repository\repository\cashier\GeneralCage_op\Excel\[OperationalProcedureInventory.xls]Sheet1'!$G:$G,D4)

--------------------


In the above example, $G:$G is a column containing various values. I am
trying to get the formula to count how many rows are of a given value.


Anyone?

 

[Harlan Grove]

I am trying to reference an outside workbook with the COUNTIF() function
and keep getting #VALUE errors.

Here is an example of what I am trying to do: ...
=COUNTIF('\\server1\share\PFA_repository\repository\cashier\GeneralCage_op\
Excel\[OperationalProcedureInventory.xls]Sheet1'!$G:$G,D4)

...

Looks like this particular file is closed. COUNTIF and SUMIF can only work with
1st argument references to ranges in *open* workbooks only. The technical reason
is that the 1st argument to COUNTIF and SUMIF must be a range object, but
external references to ranges in closed workbooks aren't range objects, they're
arrays. Only when you open such workbooks do external references to their ranges
convert from arrays to range objects.

So, how can you do this? Use SUMPRODUCT, but you won't be able to address the
entire column G. Most worksheet functions can't process entire columns. If you
need to go through all of column G, try

=SUMPRODUCT(--('\\server1\share\PFA_repository\repository\cashier\
GeneralCage_op\Excel\
[OperationalProcedureInventory.xls]Sheet1'!$G$1:$G$65535=D4))
+('\\server1\share\PFA_repository\repository\cashier\GeneralCage_op\Excel\
[OperationalProcedureInventory.xls]Sheet1'!$G$65536=D4)