Data Import & Export

Fix Excel SUMIFS #VALUE! Errors with Closed OneDrive Workbooks

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

The user needs Excel SUMIFS to calculate using data from a OneDrive workbook without returning an error when that workbook is closed.

Fix Excel SUMIFS #VALUE! Errors with Closed OneDrive Workbooks
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.
Before you start

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.

Solution 1Recommended

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.

1
Connect to the source

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.

2
Select the source data

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.

3
Load the query

In Power Query, select Close & Load to load the data into the destination workbook as a table.

4
Update and refresh

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.

Import the OneDrive data with Power Query
Data updates: The imported table reflects the last successful refresh, not necessarily the current contents of the source workbook.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceCompatible with Microsoft Excel XLS and XLSX formatsUse spreadsheet formulas and work with tabular dataFree and lightweight for everyday Office tasks
microsoft office alternative - wps office

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.