Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing External files with slightly different names | Excel Worksheet Functions | |||
Referencing External Data | Excel Discussion (Misc queries) | |||
Indirect referencing of external spreadsheets | Excel Discussion (Misc queries) | |||
External References - Forcing Relative File Referencing | Excel Worksheet Functions | |||
External links referencing URL with parameter | Excel Discussion (Misc queries) |