Countif failing when referencing an external file
 

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

Countif failing when referencing an external file
 

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))


"Alex" <@ wrote in message ...
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