Formula Errors

Fix #VALUE! Errors in Excel Formulas Linked to Closed Workbooks

Maira MehtabMaira Mehtab Nov 20, 2026 870 views

Question details

Find a way to calculate linked Excel formulas when their source workbooks are closed.

Fix #VALUE! Errors in Excel Formulas Linked to Closed Workbooks
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the formula cell

In Excel, open the destination workbook and select the cell containing the COUNTIFS formula.

2
Enter separate criteria tests

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

3
Adjust the references

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.

4
Test with the source closed

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.

Replace a COUNTIFS formula with SUMPRODUCT
Text matching and errors: The SEARCH example matches text containing East; it is not an exact match. For an exact match, use a test such as --('[Source.xlsx]Sheet1'!$A$2:$A$100="East"). Check source cells for errors if the revised formula still fails.
Free Microsoft Office alternative

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.

Review and edit spreadsheet formulas in a familiar interfaceCompatible with Microsoft Excel XLS and XLSX formatsLightweight tools for everyday spreadsheet workOpen existing Office files to make migration easier
QA img-9

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.