Using =SUMIFS formula between different spreadsheets on SharePoint

Copper Contributor

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?

 

7 Replies

@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)

  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.

Thank you for your help. I will work through this now.

@NikolinoDE 

Hi can you please show how would the final link look like when doing all the replacements?

Thank you

@Naira36

 

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.

@NikolinoDE Thank you for your help. I will bear that in mind when I set up the next spreadsheet. I went and combined all the spreadsheets into one spreadsheet with multiple tabs and it worked perfectly after that.

Thank you for your help.

Regards

Peter

I'm glad you found a solution and I wish you much success with Excel.

@NikolinoDE 

 

Thank you, unfortunately it did not work. I tried pasting data with link on the source data but it blocks the file.

regardless thank you for your help.