Using =SUMIFS formula between different spreadsheets on SharePoint

Geordie33 

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:

1. 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.
2. 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.
1. 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.
2. 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.
3. 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.