External References in Functions

[Guest]

I'm having trouble with an external reference in one of my Excel Functions.
The general form of my function is:

=COUNTIF( external reference to a range, a value)

When I open my worksheet and tell it to update the data, the cells with this
formula indicate that there is an error with my function. But then, when I
open the workbook containing the external reference, my formulas miraculously
correct themselves.

Just some additional info. -- I have other formulas that work which
reference the same external data.

If anybody could give me a clue to what might be going on, I'd surely
appriciate it. I've pasted the actual formulas that I've used in case
anybody is willing to take a close look at what I've done .

Thanks in advance,

-Greg

Formula that doesn't work
=COUNTIF('E:\ResearchData_Math90\AlteredDataAndSpreadsheets\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$D$2:$D$416, B3)

Formula That Works:
{=SUM( IF(
(NOT('E:\ResearchData_Math90\AlteredDataAndSpreadsheets\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$B$2:$B$416=5925))
*
('E:\ResearchData_Math90\AlteredDataAndSpreadsheets\QueryResults\[math090_A453_CompleteInfoQueryResults.xls]math090_A453_CompleteInfoQueryR'!$D$2:$D$416=B3),1,0))}

 

[Harlan Grove]

I'm having trouble with an external reference in one of my Excel
Functions. The general form of my function is:

=COUNTIF( external reference to a range, a value)

This is a FORMULA that calls the COUNTIF function.

When I open my worksheet and tell it to update the data, the cells
with this formula indicate that there is an error with my function.
But then, when I open the workbook containing the external reference,
my formulas miraculously correct themselves.

....

That's because COUNTIF requires range references as first argument,
and ranges as instantiated Excel objects can only exist in OPEN
workbooks. Excel evaluates external references into closed workbooks
as arrays. That means when the other workbook is open, Excel can pass
COUNTIF a reference to that range; but when the other workbook is
closed, Excel can only pass an array to COUNTIF. To COUNTIF, that's a
critical difference.

If you may be referring to, er, blocks of cells in closed workbooks,
you need to use SUMPRODUCT rather than COUNTIF. Using your example
above, change your formulas to

=SUMPRODUCT(--( external reference to a range = a value ))

This has been a known problem with COUNTIF (and SUMIF) for years.
There's no work-around using COUNTIF. You have to use a different
function when referring to possibly closed workbooks.

 

[Guest]

Thanks. This helps.

-Greg

This is a FORMULA that calls the COUNTIF function.

....

That's because COUNTIF requires range references as first argument,
and ranges as instantiated Excel objects can only exist in OPEN
workbooks. Excel evaluates external references into closed workbooks
as arrays. That means when the other workbook is open, Excel can pass
COUNTIF a reference to that range; but when the other workbook is
closed, Excel can only pass an array to COUNTIF. To COUNTIF, that's a
critical difference.

If you may be referring to, er, blocks of cells in closed workbooks,
you need to use SUMPRODUCT rather than COUNTIF. Using your example
above, change your formulas to

=SUMPRODUCT(--( external reference to a range = a value ))

This has been a known problem with COUNTIF (and SUMIF) for years.
There's no work-around using COUNTIF. You have to use a different
function when referring to possibly closed workbooks.