'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix SUMIFS #VALUE! Error with SharePoint Workbook Links in Excel
Question details
The user encounters a #VALUE! error when using the SUMIFS function to pull data from a linked SharePoint workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Linking data between two SharePoint workbooks where the source file is replaced daily and might be closed or opened in Excel for the web.
- Observed behavior
- A standard SUM function works perfectly across the linked workbooks, but the SUMIFS function results in a #VALUE! error under the same conditions.
Verify that your external workbook links are up to date, especially if the source file is replaced daily with a slightly different filename.
Open Both Workbooks in the Excel Desktop Application
This is the most reliable method, as SUMIFS cannot process cross-workbook links properly if the source workbook is closed or opened in Excel for the web.
The SUMIFS function, along with COUNTIFS and AVERAGEIFS, has a known limitation in Microsoft Excel: it cannot evaluate criteria against external workbooks unless those workbooks are actively open in the desktop client. Opening the files in Excel for the web is not sufficient to resolve the link evaluation.
Navigate to your SharePoint document library where the daily source workbook is stored.
Select the source workbook, click the three-dot menu (More options), and choose 'Open in Desktop App'.
Repeat the previous step to open your destination workbook (the one containing the SUMIFS formula) in the Excel desktop application as well.
Check the cells with the SUMIFS formulas. The #VALUE! error should now be resolved, and the correct totals will display.
Use an Alternative Array-Based Formula
If you need the formula to calculate correctly even when the source workbook remains closed, replace SUMIFS with an array-supported function.
Experience Hassle-Free Data Management with WPS Office
If you frequently encounter formula limitations, sync errors, and compatibility issues with SharePoint and Microsoft Excel for the web, consider switching to WPS Office. It provides a lightweight, highly compatible, and completely free alternative for all your spreadsheet tasks.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the application: Run the installer and follow the on-screen instructions to set up WPS Office on your computer.
- 3. Open your workbooks: Launch WPS Spreadsheet and open your existing .xlsx files to enjoy fast, error-free formula calculations.
Frequently Asked Questions
Why does a standard SUM formula work with closed workbooks, but SUMIFS does not?
The SUM function is a basic mathematical function that can easily read the cached values of external workbooks. However, functions that evaluate criteria across ranges (like SUMIFS, COUNTIFS, and AVERAGEIFS) require the external workbook to be open in the desktop app to actively process the logic. If it is closed, Excel returns a #VALUE! error.
Can I use Excel for the web to bypass this SUMIFS error?
No. Excel for the web does not reliably support complex cross-workbook formula evaluations like SUMIFS. Even if both workbooks are open in your browser, the #VALUE! error may persist. You must use the desktop application for full cross-workbook link support.
How do I update the formula if the SharePoint file is replaced and renamed every day?
You do not need to rewrite the formula manually. Go to the Data tab in the Excel ribbon, click on 'Edit Links' (or 'Workbook Links'), select the old source file, and click 'Change Source'. Select the new file for the day, and all formulas referencing the external link will update automatically.
