Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

How to Sum Multiple Excel Tables Across Sheets

Date:2019-9-19 Author:Cordelia

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.

How to Sum Multiple Excel Tables Across Sheets

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.

How to Sum Multiple Excel Tables Across Sheets

Or click the arrow beside Paste Special… to expand the menu.

How to Sum Multiple Excel Tables Across Sheets

Then click Paste Special… here.

How to Sum Multiple Excel Tables Across Sheets

4. When the Paste Special pop out, select Add in the Operation section. Click OK to implement the pasting.

How to Sum Multiple Excel Tables Across Sheets

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 Multiple Excel Tables Across Sheets

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.

How to Sum Multiple Excel Tables Across Sheets

2. Enter the formula in C2: =SUM(‘Table 1′!B2,’Table 2′!B2,’Table 3’!B2)

How to Sum Multiple Excel Tables Across Sheets

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.

How to Sum Multiple Excel Tables Across Sheets

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *