Formula Errors

Fix Excel COUNTIFS #VALUE! Errors When a Source Workbook Is Closed

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

The user needs a formula to count matching rows when the referenced Excel workbook is closed.

Fix Excel COUNTIFS #VALUE! Errors When a Source Workbook Is Closed
Product
Microsoft Excel
Device & OS
not provided
Scenario
A COUNTIFS formula references ranges in another workbook and must recalculate after that workbook is closed.
Observed behavior
The formula returns the correct count while the source workbook is open but returns #VALUE! after the source is closed and Excel recalculates.
Before you start

Open both workbooks and confirm that the source sheet, ranges, and criteria cells in the formula are correct. Save the source workbook so the external link can use its latest saved data.

Solution 1Recommended

Replace COUNTIFS with SUMPRODUCT

Use SUMPRODUCT to count rows that meet all criteria when the source workbook is closed.

COUNTIFS can return #VALUE! when its criteria ranges refer to a closed workbook. SUMPRODUCT can evaluate equivalent comparisons against external ranges.

The example counts rows where columns J, A, and K match V1, S1, and Y1 respectively. All three source ranges must cover the same number of rows.

1
Select the formula cell

With both workbooks open, select the cell containing the COUNTIFS formula in the destination workbook.

2
Enter a SUMPRODUCT formula

In the formula bar, replace COUNTIFS with a formula such as: =SUMPRODUCT(('[2019 Production Backlog.xlsx]Backlog Report-2019 SN'!$J$2:$J$10485=$V$1)*('[2019 Production Backlog.xlsx]Backlog Report-2019 SN'!$A$2:$A$10485=$S$1)*('[2019 Production Backlog.xlsx]Backlog Report-2019 SN'!$K$2:$K$10485=$Y$1)). Adjust the workbook, sheet, ranges, and criteria cells to match your files, then press Enter.

3
Test the closed-workbook link

Save and close the source workbook, then recalculate the destination workbook. Confirm that the formula still returns the expected count.

Replace COUNTIFS with SUMPRODUCT
External links and saved data: Excel may add the source file path to the formula when the workbook is closed. The source file must remain accessible, and closed-workbook results reflect the available saved data.
Free Microsoft Office alternative

Work with spreadsheet formulas in WPS Office

WPS Office is a free, lightweight Microsoft Office alternative with a familiar interface for opening and editing spreadsheets. Its format compatibility makes it easier to move existing files, though it does not fix Excel's closed-workbook limitation.

Work with spreadsheet formulas and linked data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface when moving from Microsoft OfficeAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS work only when the source workbook is open?

COUNTIFS has a known limitation with criteria ranges in closed external workbooks. Replace it with an equivalent SUMPRODUCT formula, or keep the source workbook open during recalculation.

How do I count rows that match several criteria with SUMPRODUCT?

In the formula bar, multiply comparisons inside SUMPRODUCT, such as =SUMPRODUCT((range1=criterion1)*(range2=criterion2)). Use equally sized ranges and reference the appropriate source workbook and sheet.

Why is my SUMPRODUCT formula still returning an error?

Check the formula bar for mismatched range sizes, errors in the referenced cells, or an incorrect external workbook or sheet reference. Open the source workbook to verify the ranges and save it before testing again.

Will a closed-workbook formula include unsaved changes?

No. Save the source workbook before closing it so Excel can use its latest saved data when updating the external link.