Fix #VALUE! Errors in Excel Formulas Linked to Closed Workbooks
Question details
Find a way to calculate linked Excel formulas when their source workbooks are closed.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formulas use functions such as COUNTIFS, SUMIFS, or INDIRECT to reference data in another workbook.
- Observed behavior
- Some formulas return #VALUE! when the referenced workbook is closed.
Identify the formulas returning #VALUE! and check the source workbook name, sheet name, and referenced ranges. Open the source workbook so you can inspect the data and build or verify the external references.
Replace a COUNTIFS formula with SUMPRODUCT
Use SUMPRODUCT to count matching records when a COUNTIFS formula cannot calculate against a closed source workbook.
Use separate tests for each condition and make sure all referenced ranges have the same dimensions. SUMPRODUCT does not directly replace every use of SUMIFS or INDIRECT; the formula must be adapted to the calculation.
In Excel, open the destination workbook and select the cell containing the COUNTIFS formula.
In the formula bar, replace the formula with a SUMPRODUCT expression. For example, to count rows where column A contains East and column B contains a number greater than 5, use =SUMPRODUCT(--ISNUMBER(SEARCH("East",'[Source.xlsx]Sheet1'!$A$2:$A$100)),--ISNUMBER('[Source.xlsx]Sheet1'!$B$2:$B$100),--('[Source.xlsx]Sheet1'!$B$2:$B$100>5)).
Replace Source.xlsx, Sheet1, and the ranges in the formula bar with your actual workbook, sheet, and data ranges. With the source workbook open, you can select its ranges to insert the references.
Save the destination workbook, close the source workbook, and recalculate the destination workbook. If prompted, allow Excel to update the workbook links, then check the result.

Open the source workbook to calculate the existing formula
Use this option when you need to keep the existing formula, especially one that uses INDIRECT.
Work with Linked Spreadsheet Data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel files to work with formulas and migrate spreadsheets, while checking linked formulas for compatibility.

Frequently Asked Questions
Why does COUNTIFS return #VALUE! when a linked workbook is closed?
Some Excel criteria functions cannot evaluate external ranges reliably when the source workbook is closed. Open the source file or adapt the calculation with SUMPRODUCT and direct external references.
Can INDIRECT reference a closed Excel workbook?
INDIRECT generally requires the referenced workbook to be open. If the reference does not need to be dynamic, try replacing INDIRECT in the formula bar with a direct external cell or range reference.
How do I count text containing a word and numbers above a threshold?
Use SUMPRODUCT with separate tests, such as ISNUMBER(SEARCH("East", text_range)), ISNUMBER(number_range), and number_range>5. Make sure the ranges cover the same number of rows.
Why does my SUMPRODUCT formula still return an error?
Check that the external workbook and sheet references are correct, the ranges have matching dimensions, and the source cells do not contain errors. Open the source workbook and inspect the ranges selected in the formula bar.




