Forum Discussion
Using =SUMIFS formula between different spreadsheets on SharePoint
I am trying to reduce manual entry into one Excel spreadsheet by using =SUMIFS formula to get information from 3 other different spreadsheets. When I use just normal Excel it works find, but using the formula for the files that are loaded to our SharePoint, it just gives back a zero result. Is there something I need to activate to allow all four spreadsheets to be link through my formulas?
When you are working with Excel Online (Excel for the web) and you want to use the =SUMIFS formula to get information from different spreadsheets on SharePoint, there are a few considerations to ensure everything works smoothly:
- SharePoint Permissions: First, ensure that you have the necessary permissions to access and edit all the files on SharePoint. Without proper permissions, you might not be able to link the files through your formulas.
- File Paths: In Excel for the web, you will need to use the full file path to reference files stored on SharePoint. This is especially important if the files are located in different folders or subfolders. The file path should be included in the formula as follows:
=SUMIFS('[https://YourSharePointURL.com/YourSite/YourLibrary/YourFile.xlsx]SheetName'!A1:A10, CriteriaRange, Criteria)
- Replace 'https://YourSharePointURL.com/YourSite/YourLibrary/YourFile.xlsx' with the actual URL of your SharePoint file.
- Replace 'SheetName' with the name of the worksheet in the target file.
- Adjust the 'A1:A10', CriteriaRange, and Criteria as needed for your specific formula.
- Refresh Data: Excel for the web might not update linked data in real-time. You might need to manually refresh the data in your worksheet or set up an automatic refresh interval through the SharePoint settings. To refresh data, you can use the "Refresh" button in Excel for the web or use the "Data" tab to set an automatic refresh schedule.
- Data Source Sharing: Ensure that the files on SharePoint are shared or accessible to others who need to view or edit them. Sharing settings for each file should be adjusted accordingly.
- Cell Reference: Make sure that the cell reference within your SUMIFS formula accurately corresponds to the data you want to sum from the target file.
By following these steps, you should be able to link and use data from different spreadsheets stored on SharePoint in your Excel for the web formulas. If you still encounter issues, it is a good practice to check SharePoint and Office 365 settings to ensure that sharing and permissions are properly configured. The text, steps and formulas was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
Hi can you please show how would the final link look like when doing all the replacements?
Thank you
Let us say you have a file named "SalesData.xlsx" stored in a SharePoint library named "Documents" within a site named "SalesSite". You want to sum the sales amounts from a worksheet named "Sales" in this file based on certain criteria. Here is how the final link would look like after replacing placeholders:
=SUMIFS('[https://YourSharePointURL.com/SalesSite/Documents/SalesData.xlsx]Sales'!A1:A100, '[https://YourSharePointURL.com/SalesSite/Documents/SalesData.xlsx]Sales'!B1:B100, "ProductA")
In this formula:
- 'https://YourSharePointURL.com/SalesSite/Documents/SalesData.xlsx' is the full URL of the SharePoint file.
- 'Sales' is the name of the worksheet within the file.
- A1:A100 is the range of sales amounts you want to sum.
- B1:B100 is the range of criteria (e.g., product names).
- "ProductA" is the criteria you're using to filter the sales amounts.
Make sure to replace 'https://YourSharePointURL.com/SalesSite/Documents/SalesData.xlsx' with the actual URL of your SharePoint file, and adjust the worksheet name, ranges, and criteria as needed for your specific scenario. The text are edit with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Thank you for your help. I will work through this now.
