Formula Errors

How to Count Rows in a Closed External Excel Workbook Using SUMPRODUCT

Bushra Parveen Oct 10, 2026 870 views

Question details

The user needs a method to count records in an external workbook that remains closed, matching specific text and date criteria.

How to Count Rows in a Closed External Excel Workbook Using SUMPRODUCT

Product: Excel
Device & OS: not provided
Scenario: Counting specific rows in a separate, closed workbook where conditions (such as a specific support category and a date within the last five days) must be met simultaneously.
Observed behavior: Standard functions like COUNTIF and SUMIF return a #VALUE! error when querying a closed workbook, and standard SUMPRODUCT attempts are resulting in invalid formula errors.

Before you start

Verify the exact file path, file name, and sheet name of your external workbook. Ensure that both workbooks are saved on your local drive or accessible network location before creating the formula.

Solution 1Recommended

Use SUMPRODUCT with Multiplied Boolean Criteria

Unlike COUNTIF or SUMIFS, the SUMPRODUCT function can evaluate arrays in closed workbooks by multiplying TRUE/FALSE conditions.

The standard COUNTIF and SUMIF functions are strictly designed to evaluate ranges in open workbooks. When the source file is closed, Excel cannot resolve the reference, leading to a #VALUE! error.

By utilizing the SUMPRODUCT function and multiplying boolean arrays, you force Excel to calculate the TRUE (1) and FALSE (0) values natively, which successfully retrieves data from closed external files.

Select the destination cell

Click on the cell in your active workbook where you want the final count to appear.

Input the SUMPRODUCT formula

Type the formula structure that multiplies your specific criteria arrays. For example: =SUMPRODUCT(([Active.xlsx]Sheet1!$K$2:$K$10000="Applications Support")*([Active.xlsx]Sheet1!$C$2:$C$10000>=TODAY()-5))

Include the full file path

If the external workbook is completely closed, ensure the formula includes the full directory path. Excel typically updates this automatically if you write the formula while the file is open and then close it, changing it to something like =SUMPRODUCT(('C:\Reports\[Active.xlsx]Sheet1'!$K$2:$K$10000="Applications Support")...)

Execute the calculation

Press Enter. Excel will calculate the product of the arrays and sum the corresponding rows, giving you the accurate count of records matching your criteria.

Use SUMPRODUCT with Multiplied Boolean Criteria

Crucial Performance Tip: Always use matching, limited ranges (such as $K$2:$K$10000) instead of entire columns (like $K:$K). Referencing full columns in SUMPRODUCT arrays forces the application to evaluate millions of empty cells, drastically reducing performance and causing application freezes.

Count Rows in External Workbooks Seamlessly with WPS Spreadsheet

WPS Office offers a robust calculation engine that fully supports advanced array formulas like SUMPRODUCT. It allows you to seamlessly link external workbooks, calculate multiple criteria effortlessly, and maintain high performance even with complex cross-workbook referencing.

1. Open your summary document: Launch WPS Spreadsheet and open the master workbook where you want to display the calculation results.
2. Input the array formula: Type your =SUMPRODUCT(...) formula using array multiplication for your specific criteria.
3. Reference the external file: Include the closed external .xlsx file path directly within the formula arguments.
4. Calculate instantly: Press Enter to execute the formula and instantly count the matching records without needing to open the source file.

100% compatibility with Microsoft Excel formulas and .xlsx file formats.Advanced calculation engine designed to handle large arrays and external references smoothly.Lightweight software architecture that ensures fast loading speeds for heavy data files.

microsoft office alternative - wps office

Frequently Asked Questions

Why do COUNTIF and SUMIF return a #VALUE! error with closed workbooks?

Functions like COUNTIF, COUNTIFS, SUMIF, and SUMIFS require the referenced workbook to be open in the system's memory to evaluate the criteria arguments. When the source workbook is closed, these functions cannot resolve the paths and instantly return a #VALUE! error. SUMPRODUCT does not share this limitation and processes the arrays natively.

Do I need to include the full file path manually in the SUMPRODUCT formula?

If you are writing the formula while the external workbook is closed, you must include the full file path enclosed in single quotes. Alternatively, a best practice is to open both workbooks, build the formula (which only requires the workbook name), and then close the external workbook. The application will automatically update the formula to include the full file path.

Can I use the FILTER function instead of SUMPRODUCT for closed workbooks?

In newer versions of spreadsheet software, you can sometimes use the FILTER function combined with ROWS or COUNTA to extract or count matching records. However, dynamic array functions like FILTER may still encounter reference errors if the source workbook is closed, making SUMPRODUCT the most reliable method for closed-file calculations.

Excel & Data Problems