It’s common to input data in different Excel spreadsheets by different people and sum them later. But as you know, if you copy a column of data and copy them into another table, the existing data will be overwritten. Is there a way to sum the data across multiple Excel tables in different sheets?
How to Sum Two Excel Tables
If there are only 2 tables you want to sum, using Paste Special should be easier than using Excel functions.
1. First, select the cells you want to sum in Table 1 and press [Ctrl+C] to copy them.
2. Then switch to Table 2 (no matter in the same sheet or anther sheet), and select the same range of cells as in Table 1.
3. Next, right-click the selected area, click Paste Special… directly under Paste Options.
Or click the arrow beside Paste Special… to expand the menu.
Then click Paste Special… here.
4. When the Paste Special pop out, select Add in the Operation section. Click OK to implement the pasting.
5. Then the selected data in Table 1 will be added to the same range of data in Table 2. And the calculation result of summation will show immediately without any formula.
How to Sum More than Two Spreadsheets
With more than two tables in different sheets, Paste Special can hardly help you. You can use SUM function to achieve the same goal.
Assuming there are 3 tables to sum – Table 1, Table 2 and Table 3.
1. If you want to calculate the summation in Table 3, then create a new column in it.
2. Enter the formula in C2: =SUM(‘Table 1′!B2,’Table 2′!B2,’Table 3’!B2)
The content inside ” refers to the sheet name; the content after ! refers to the data you want to sum in this row. So this formula aims at calculating the summation of B2 in Table 1, Table 2 and Table 3.
3. Press [Enter] to work it out, and drag your mouse to use Fill Handle applying the same formula to the cells below.
4. Now all the values of column B in the 3 tables have been summed up. You can use this formula to sum even more tables.
Leave a Reply