Use SUM Across Two Excel Workbooks Stored on SharePoint
Question details
Create a SUM formula in one Excel workbook that totals cells from a second workbook stored in SharePoint.
- Product
- Microsoft Excel workbooks stored on SharePoint
- Device & OS
- not provided
- Scenario
- The user needs to calculate a total in a destination workbook using a cell range from another online Excel workbook.
- Observed behavior
- Excel needs to generate an external workbook reference that includes the SharePoint location, source file, worksheet, and cell range.
Make sure you have permission to open both SharePoint workbooks and that the source workbook has been saved with its final file name and location. Use the desktop Excel app if Excel for the web does not allow you to create or edit the external reference.
Create the SharePoint external SUM reference while both workbooks are open
Use this method to let Excel build the correct SharePoint path, workbook name, sheet name, and source range automatically.
Opening both files from SharePoint before selecting the range helps prevent typing errors in the external workbook URL. Excel converts the selected range into an external reference in the SUM formula.
In SharePoint, open the workbook where you want to display the total, then select the cell that should contain the SUM result.
Open the second SharePoint workbook in Excel as well, keeping the destination workbook open in another window.
In the destination cell, type =SUM( but do not press Enter yet.
Switch to the source workbook, open the required worksheet, and drag to select the cells to total, such as A1:A100.
Type ) or press Enter to finish the formula. Excel creates a reference similar to =SUM('https://tenant.sharepoint.com/sites/SiteName/Shared Documents/folder/[FileName.xlsx]SheetName'!$A$1:$A$100).
Check the workbook path and refresh the external link
Use this option when the formula returns an error, a stale value, or a prompt to update links after the source file was moved or renamed.
Work with Excel SUM Formulas in WPS Office
If you need a free, lightweight Office suite for creating and editing spreadsheet calculations, WPS Office provides a familiar spreadsheet workspace and supports common Excel formulas. You can download SharePoint files locally or open supported cloud-synced files, then continue working with compatible XLSX spreadsheets during migration from Microsoft Office.
- 1. Download WPS Office: Select Download WPS Office Free and install WPS Office on your device.
- 2. Open the spreadsheet: In WPS Spreadsheets, select Menu > Open and choose your downloaded Excel workbook.
- 3. Review formulas: Select a formula cell to review or edit its SUM calculation in the formula bar.
Frequently Asked Questions
Can I create a SUM link between two Excel workbooks in SharePoint?
Yes. Open both workbooks, type =SUM( in the destination workbook, then select the range in the source workbook. Excel creates an external reference to the SharePoint file.
Why should both SharePoint workbooks be open when I create the formula?
Keeping both files open lets Excel insert the correct SharePoint URL, workbook name, sheet name, and absolute cell range without manually building the link.
What does a SharePoint external reference look like in Excel?
It commonly includes the SharePoint path, source workbook in brackets, worksheet name, and range, such as =SUM('https://tenant.sharepoint.com/sites/Site/Shared Documents/[Source.xlsx]Sheet1'!$A$1:$A$100).
Why does my linked SUM formula show an error after a file move?
The external link may point to the previous folder or file name. Restore the source file location or use Data > Queries & Connections > Edit Links in desktop Excel to update the link.
Can Excel for the web create external workbook references?
Support can vary by browser, organization settings, and workbook configuration. If the formula cannot be created or edited online, open both SharePoint workbooks in the desktop Excel app and create the reference there.
