Combining the sum of the same cells of an entire onedrive folder in excel

Question

I am making a spreadsheet to summarize a big project, with continuous data being updated daily. My team updates and adds new workbooks daily. Everything is located on a one drive. My question is: how to I sum of a cells of the same workbook across and entire folder that is located on a onedrive. Example: On the summary workbook, I want it to add all cells I57 from all workbooks located in a folder on onedrive. Thanks for the info.

Answer 1

Hi @Daniel Murphy,

To sum cells of the same workbook across an entire folder located on OneDrive, you can use Power Automate flow to combine the worksheets into a single workbook and then use the SUM function to add up the values of the desired cells. Follow the steps below:

1. Create a new Excel file in your OneDrive. The file name "Combination.xlsx" is used in this sample.
2. Create and save the two scripts from this sample: "Return worksheet data" and "Add worksheets".
3. Create a folder in your OneDrive and add one or more workbooks with data to it. The folder name "output" is used in this sample.
4. Build a flow (as described in the Power Automate flow section of this article) to perform these steps:
5. List all the files in the "output" folder.
6. Use the "Return worksheet data" script to get the data from every worksheet in each of the workbooks.
7. Use the "Add worksheets" script to create a new worksheet in the "Combination.xlsx" workbook for every worksheet in all the other files.
8. In the "Combination.xlsx" workbook, use the SUM function to add up the values of the desired cells.

Reference:

• Combine worksheets into a single workbook

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.