Countif failing when referencing an external file

[Alex]

Hi

Something wierd is happening with a COUNTIF formula I have set up...

I have two spreadsheets (let's call them "Summary" and "Detail")

Column A on the Detail spreadsheet contains a variable amount of numbers

The Summary spreadsheet has two formulas, both of which reference the Detail
spreadsheet. The first formula counts the amount of numbers in column A
with the formula:

=COUNT('[Detail.xls]Sheet 1'!$A$1:$A$100)

The second formula interegates the same range on the Detail spreadsheet, but
counts the number of times the number in the range is less than 10, with the
formula:

=COUNTIF('[Detail.xls]Sheet 1'!$A$1:$A$100,"<10")

When the Detail spreadsheet is open, both formulas work fine. However, when
I close the Detail spreadsheet, the COUNTIF formula returns #VALUE!. The
COUNT formula continues to work OK.

Any ideas why the COUNTIF formula is failing?

Thanks in advance
Alex

 

[Don Guillett]

Some functions, such as indirect, do not work with closed wbs. try

=SUMPRODUCT(([filename]Sheet1!$A$1:$A$15=1)*([filename]Sheet1!$A$1:$A$15))
=sumproduct('[Detail.xls]Sheet 1'!$A$1:$A$100<10)*1)
or
sumproduct('[Detail.xls]Sheet 1'!$A$1:$A$100<10)*([Detail.xls]Sheet
1'!$A$1:$A$100))