Fix Excel COUNTIFS #VALUE! Errors When a Source Workbook Is Closed
Question details
The user needs a formula to count matching rows when the referenced Excel 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.
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.
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.
With both workbooks open, select the cell containing the COUNTIFS formula in the destination workbook.
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.
Save and close the source workbook, then recalculate the destination workbook. Confirm that the formula still returns the expected count.

Keep the source workbook open while using COUNTIFS
Use this workaround if you want to retain the existing COUNTIFS formula.
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.

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.




