Summing multiple workbooks

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
Hi,

I know I came across this somewhere but I just couldn't recall where I have seen this been done with regard to summing across multiple worksheet.

Here is my problem. I have about 50 workbooks with similar layout and I have a summary workbook that basically sum up all value of these 50 workbooks. That mean in the Summary Workbook, cell A1 will be the summation of Workbook 1 to 50 and that is the same for Cell A2 and so on.

What I want to know if there is a easier way of summing up all these workbooks without having to open all of them and going to individual cell to achieve the desired result.

Many thanks for any suggestion :biggrin:
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
I have also another thing to do and that is the number of workbooks may be increase from time to time so it is not limited to these 50 workbooks.

Thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Do you mean 50 worksheets in the same workbook? or 50 separate workbooks?

If it's 50 worksheets in the same workbook you can use a 3D reference eg. =SUM(Sheet1:Sheet50!A2) will sum the contents of all cells A2 in sheets 1 to 50.
 

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
Fergus,

The 50 worksheets are in different workbooks. Although the worksheet name is the same for all 50 workbooks.
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Then I don't know of an easy way to do what you want. Personally I would use vba and use some easy system to name the workbooks eg. wkbk1, wkbk2, and then use a loop in the vba to loop through "wkbk"&x where x goes from 1 to 50.
 

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
504
Fergus,

Thanks for your help. I hope one of the gurus can help with my problem. It kinda of urgent for me.
 

Forum statistics

Threads
1,136,878
Messages
5,678,324
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top