Fix SUMIFS Returning Zero in Excel Files Stored on SharePoint
Question details
The user needs to troubleshoot a SUMIFS formula that returns zero when its source ranges are stored in other SharePoint workbooks.
- Product
- Microsoft Excel and Excel for the web
- Device & OS
- not provided
- Scenario
- A workbook uses SUMIFS with external references to files stored on SharePoint, and the user needs the formula to calculate the expected result.
- Observed behavior
- SUMIFS returns 0, potentially because of missing permissions, incorrect external paths or ranges, unsynchronized files, or Excel for the web limitations with external workbook links.
Confirm that the SharePoint source workbooks are online, shared with the account running the formula, and available for desktop Excel to open. Have the exact source file names, worksheet names, ranges, and criteria values ready for comparison with the formula.
Verify SharePoint permissions and SUMIFS external references
Use this solution when the formula may point to an inaccessible file, incorrect worksheet, invalid range, or mismatched criteria.
A SUMIFS formula can return zero when Excel cannot read an external workbook or when any referenced file, sheet, range, or criteria value is incorrect. Check access and the complete external reference before changing the formula structure.
Open each source workbook from the SharePoint document library with the same Microsoft account used for the destination workbook. Confirm that the files are shared with you and are available online.
In Excel, select the SUMIFS cell and review the formula in the formula bar. Check the SharePoint file path, source workbook name, worksheet name, sum range, criteria ranges, and criteria values for spelling and range mismatches.
Check the criteria cells and source data for extra spaces, different text, dates stored as text, or number-format differences. Use matching ranges with the same row and column boundaries.
In desktop Excel, open the Data tab and use Refresh All or Edit Links to update the linked source workbooks, then save both the source and destination files.
Combine SharePoint files with Power Query
Use this approach when many SharePoint workbooks are involved or external SUMIFS links remain unreliable.
Review and manage SharePoint Excel workbooks with WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for opening, reviewing, and editing Excel workbooks while you troubleshoot formulas or migrate a workflow. Its familiar spreadsheet interface and XLSX compatibility make it easy to inspect SUMIFS formulas and move files between applications, although Microsoft SharePoint permissions and Excel web link-refresh behavior must still be handled in Microsoft services.
- 1. Open the workbook: Launch WPS Spreadsheet and select Open to load the destination XLSX workbook downloaded from SharePoint.
- 2. Inspect the formula: Select the SUMIFS cell and review the formula bar to compare the external workbook path, worksheet name, ranges, and criteria.
- 3. Check the source data: Open the related source workbook in WPS Spreadsheet and verify that the referenced ranges contain matching values and criteria.
- 4. Save a compatible copy: Select Menu > Save As, choose Excel 2007-365 Workbook (*.xlsx), and save the reviewed file before returning it to the SharePoint workflow.
Frequently Asked Questions
Why does SUMIFS return zero for a SharePoint workbook?
The source workbook may be inaccessible, the external file path or worksheet name may be wrong, the ranges may not align, or the criteria may not match the source data. Excel for the web may also fail to refresh external workbook links.
How can I check whether SharePoint permissions are causing the zero result?
Open every referenced source workbook from the SharePoint library using the same account as the destination workbook. If a file cannot be opened or prompts for access, request permission and then refresh the links in desktop Excel.
Can Excel for the web update SUMIFS links to other workbooks?
Excel for the web has limitations with external workbook links and may not fully refresh or recalculate them. Open the source and destination workbooks in desktop Excel, use Data > Refresh All or Edit Links, and save the files.
What should I check in a SUMIFS external reference?
Verify the complete source file path, workbook name, worksheet name, sum range, criteria ranges, and criteria values. Also check for extra spaces, text-versus-number differences, and ranges with different row counts.
Is Power Query better than external SUMIFS links for SharePoint files?
Power Query is often more reliable when combining multiple SharePoint workbooks because it imports and transforms the files through a refreshable query instead of relying on many external cell links. Use Data > Get Data > From File > From SharePoint Folder in desktop Excel.
