How to Fix Excel SUMIFS Returning #VALUE! Error for Closed SharePoint Workbooks
Question details
The user needs to resolve a #VALUE! error in Excel caused by using the SUMIFS function to reference external data from a closed workbook stored on SharePoint.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to calculate dynamic data using the SUMIFS function linked to external source files located on SharePoint without keeping the source files open.
- Observed behavior
- The SUMIFS formula returns a #VALUE! error when the referenced SharePoint workbook is closed, even though other functions like VLOOKUP continue to work.
Verify your network connection to SharePoint and note the exact file paths of the external workbooks referenced in your formula. Ensure you have the necessary permissions to access and read the source files.
Keep the Source SharePoint Workbook Open
The most straightforward workaround for the SUMIFS external link limitation is to keep the referenced workbook open in the Excel desktop application while calculating.
Excel's architecture requires the source workbook to be actively loaded in memory for certain functions like SUMIFS, COUNTIFS, and INDIRECT to calculate correctly. If the file is closed, Excel cannot retrieve the necessary array data, resulting in a #VALUE! error.
Launch the Excel desktop application and open the source file stored on SharePoint.
Open the workbook containing your SUMIFS formula.
If automatic calculation is enabled, the #VALUE! errors will resolve immediately. If set to manual, press F9 to force a recalculation.
Replace SUMIFS with the SUMPRODUCT Function
Use the SUMPRODUCT function as an alternative, as it natively supports reading arrays from closed external workbooks without throwing a #VALUE! error.
Import Source Data Using Power Query
Use Power Query to securely import the necessary data from the SharePoint workbook into your local file, allowing SUMIFS to run locally.
Use WPS Office as a Lightweight Alternative for Your Spreadsheets
If you frequently encounter frustrating limitations with complex external links in Excel, consider using WPS Office. It provides a free, lightweight, and highly compatible alternative with powerful spreadsheet capabilities, ensuring your data processing remains smooth and efficient.
- 1. Download the software: Visit the official WPS Office website to download the free installation package.
- 2. Install and launch: Follow the on-screen instructions to install WPS Office, then open WPS Spreadsheets.
- 3. Open your files: Seamlessly open your existing Excel workbooks and continue editing with full formula support.
Frequently Asked Questions
Why does VLOOKUP work with closed workbooks but SUMIFS does not?
Excel processes different functions in fundamentally different ways. Functions like VLOOKUP, INDEX, and MATCH are designed to pull specific individual data points and can read from closed files. Array-processing functions like SUMIFS, COUNTIFS, and INDIRECT require the entire data structure to be loaded into active memory, meaning the source workbook must be open.
Will syncing the SharePoint folder to my local drive fix the SUMIFS error?
Syncing the folder via OneDrive or SharePoint can change the file path from a web URL to a local directory, which might improve link stability. However, because the fundamental limitation is related to the file state (open vs. closed), SUMIFS will still return a #VALUE! error if the locally synced source file is not open in Excel.
How can I force Excel to update external links automatically?
You can manage external link behaviors by navigating to the Data tab, selecting 'Edit Links', and clicking 'Update Values'. You can also adjust the startup prompt in 'Startup Prompt...' under the same menu to update links automatically without asking. Nonetheless, for SUMIFS specifically, the source workbook must still be opened to calculate properly.
