Use SUMIFS with Data from a Closed Excel Workbook in WPS Office
Question details
Learn how to calculate SUMIFS against data stored in a closed Excel workbook by importing the source table into the active WPS spreadsheet with Power Query.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a closed external Excel workbook containing source data and needs to reference it in SUMIFS formulas without opening the file manually.
- Observed behavior
- SUMIFS may not reliably calculate directly against ranges in a closed external workbook; the goal is to import the data into a local table and refresh it when needed.
Before importing, make sure the closed workbook is saved in a location you can access and that you know which sheet or table contains the rows needed for your SUMIFS criteria.
Import the Closed Workbook Data with Power Query and Reference the Imported Table in SUMIFS
Use this method when SUMIFS needs to calculate against an external workbook that is not open, because it brings the source table into the active WPS spreadsheet.
SUMIFS is designed to calculate against ranges in the current workbook. Importing with Power Query creates a local table that can be refreshed and used reliably by formulas.
In WPS Spreadsheets, open the workbook where you want the SUMIFS formula. Go to the Data tab and click Get Data > From File > From Workbook.
Browse to the closed Excel workbook, select it, and choose the sheet or table that contains the criteria and sum ranges.
In the import or transform preview, click Load to place the query result as a table in your active worksheet, or choose Load To to keep it in the Data Model.
Write your SUMIFS formula with sum_range, criteria_range1, criteria1, and any additional pairs pointing to the imported table columns.
Select any cell in the imported table, go to the Data or query tools area, and choose Refresh. Optionally set the connection to refresh when the workbook opens.
Import a Closed Excel Workbook with WPS Office Power Query and Use SUMIFS
WPS Office Spreadsheets can import the closed workbook through Data > Get Data > From File > From Workbook, creating a local table that SUMIFS can reference. You can refresh the query whenever the source file changes, keeping your formulas accurate.
- 1. Open the Data tab: In WPS Office Spreadsheets, go to the Data tab and click Get Data > From File > From Workbook.
- 2. Select the closed workbook: Browse to the closed Excel workbook and choose the sheet or table containing the data you need for SUMIFS.
- 3. Load the imported table: Click Load to place the data as a table in the active worksheet, or choose Load To for the Data Model.
- 4. Reference the table in SUMIFS: Create the SUMIFS formula using the imported table columns as the sum and criteria ranges.
- 5. Refresh when needed: Select a cell in the imported table and choose Refresh from the Data or query tools.
Frequently Asked Questions
Can SUMIFS directly read a range from a closed Excel workbook?
In most cases, no. SUMIFS is designed for ranges in the active workbook. Import the closed workbook data with Data > Get Data > From File > From Workbook in WPS Spreadsheets, then reference the imported table.
How do I refresh the imported data when the closed workbook changes?
Select a cell in the imported table, go to the Data tab or query tools, and choose Refresh. You can also configure the connection to refresh automatically when the workbook opens.
Do I need to open the external workbook before running the query?
No. The import only needs access to the saved file. The closed workbook can remain closed, and Power Query reads its data through the file connector.
Can I use SUMIFS with the imported Data Model instead of a worksheet table?
Yes. When you load the query to the Data Model, you can create a pivot table or use relationships, but for simple SUMIFS formulas it is usually easier to load the query to a worksheet table and reference that table.
Will this work with both .xlsx and older .xls workbooks?
Yes. WPS Spreadsheets supports Microsoft Excel formats such as .xlsx and .xls, and you can import either format through Get Data from Workbook.
