Fix Excel SUMIFS #VALUE! Errors with Closed OneDrive Workbooks
Question details
The user needs Excel SUMIFS to calculate using data from a OneDrive workbook without returning an error when that workbook is closed.

- Product
- Microsoft Excel and OneDrive
- Device & OS
- not provided
- Scenario
- A SUMIFS formula references criteria ranges in an external workbook hosted on OneDrive.
- Observed behavior
- The formula returns #VALUE! while the source workbook is closed, but opening it may temporarily remove the error.
Confirm which workbook and ranges the SUMIFS formula references, and check that you can access the source file on OneDrive. For the Power Query method, make sure your Excel version supports the required data connectors.
Import the OneDrive data with Power Query
Use a refreshable local copy of the source data so SUMIFS no longer depends on closed external ranges.
Connector names and availability vary by Excel version. For a SharePoint-hosted file, use the SharePoint Folder connector when available; a OneDrive file may require a suitable direct file URL or a synced local path.
In Excel, open the destination workbook and select Data > Get Data. Choose an available connector such as From SharePoint Folder or From Web, then enter the appropriate site or direct file URL.
Sign in if prompted, select the source workbook, and choose the table or sheet containing the SUMIFS ranges. Select Transform Data if you need to adjust columns.
In Power Query, select Close & Load to load the data into the destination workbook as a table.
Edit the SUMIFS formula to reference the imported table's local sum and criteria columns. Use Data > Refresh All whenever you need the latest source data.

Keep the linked source workbook open
Use this quick workaround when you need to calculate the existing formula without changing the data connection.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move between spreadsheet tools. It does not fix Excel's closed-workbook SUMIFS behavior.

Frequently Asked Questions
Why does SUMIFS return #VALUE! when the source workbook is closed?
Excel can return #VALUE! when SUMIFS references ranges in a closed external workbook. Opening the source workbook may allow the formula to calculate again.
Does Power Query keep the imported data automatically up to date?
The loaded table shows data from the last successful refresh. Select Data > Refresh All to retrieve changes from the source.
Can I use SUMPRODUCT instead of SUMIFS?
SUMPRODUCT may be an alternative for some formulas, but external-link behavior and performance depend on the formula and workbook. Test it with the source both open and closed before relying on it.
Which URL should I use to connect to a OneDrive workbook?
Use a connector-supported direct file URL for From Web, or use an available SharePoint connector for a SharePoint-hosted file. A sharing page URL may not provide the file contents required by the connector.




