How to Add Values from Different Tables in Excel

Excel is a powerful tool for working with data, and one of the common tasks is to sum values from different sources. In this article, we will show you how to sum two columns in two different tables in Excel, using formulas, structured references, and pivot tables.

To sum two columns in two different tables in Excel, we need to use a formula that can reference both tables and perform the addition operation. One way to do this is to use the SUM function, which can take multiple arguments and return the total of all the numbers.

The SUM function has the following syntax:

$$\text{SUM}(number1,[number2],\dots )$$

The number1 argument is required, and it can be a number, a cell reference, a range, an array, or a constant. The number2 argument is optional, and it can be any of the same types as number1. You can supply up to 255 arguments to the SUM function.

For example, the formula =SUM(1,2,3) returns 6, and the formula =SUM(A1:A10,B1:B10) returns the sum of the values in the ranges A1:A10 and B1:B10.

The Procedures

To sum two columns in two different tables in Excel, we need to follow these steps:

1. Create two tables with the data that we want to sum. For this example, we will use two tables named Table1 and Table2, with the following data:

2. Enter the formula =SUM(Table1[Col2],Table2[Col2]) in a cell where we want to display the result. This formula uses structured references to refer to the Col2 column in each table. The structured references in this formula resolve to normal references like this:

=SUM(Table1[Col2],Table2[Col2])

=SUM(B2:B6,E2:E6)

=674

3. Press Enter to complete the formula and see the result.

A Comprehensive Explanation

The formula =SUM(Table1[Col2],Table2[Col2]) works by adding the values in the Col2 column of both tables. The SUM function can take multiple arguments, and it will return the total of all the numbers.

The formula uses structured references to refer to the columns in the tables. A structured reference is a way of using a table name and a column name in a formula, instead of a normal cell reference. Structured references are optional, and they can be used with formulas both inside or outside an Excel table.

The syntax of a structured reference is:

TableName[ColumnName]

Where TableName is the name of the table, and ColumnName is the name of the column. For example, Table1[Col2] refers to the Col2 column in Table1.

The advantage of using structured references is that they are dynamic and flexible. They will automatically adjust when the table is moved, resized, or filtered. They will also work even if the tables are located on different sheets in a workbook.

A Scenario with Real Data

To illustrate how to sum two columns in two different tables in Excel, let’s consider a scenario where we have two tables with sales data for two different regions. The tables are named North and South, and they have the following data:

We want to calculate the total sales for both regions. To do this, we can use the formula =SUM(North[Sales],South[Sales]) in a cell where we want to display the result. This formula will add the values in the Sales column of both tables, and return the sum.

The formula uses structured references to refer to the Sales column in each table. The structured references in this formula resolve to normal references like this:

=SUM(North[Sales],South[Sales])

=SUM(B2:B6,E2:E6)

=5500

The result is 5500, which is the total sales for both regions.

Other Approaches

Besides using the SUM function and structured references, there are other ways to sum two columns in two different tables in Excel. Here are some alternative approaches:

• Use the SUMIF or SUMIFS functions to sum values based on one or more criteria. For example, if we want to sum the values in both tables that have the letter P in the Product column, we can use the formula =SUMIF(North[Product],"*P*",North[Sales])+SUMIF(South[Product],"*P*",South[Sales]). This formula will add the values in the Sales column of both tables, only if the corresponding value in the Product column contains the letter P.
• Use the SUMPRODUCT function to multiply and sum arrays of values. For example, if we want to sum the values in both tables that are greater than 500, we can use the formula =SUMPRODUCT((North[Sales]>500)*North[Sales])+SUMPRODUCT((South[Sales]>500)*South[Sales]). This formula will multiply the values in the Sales column of both tables by a logical array that returns TRUE or FALSE, depending on whether the value is greater than 500 or not. Then, it will add the resulting arrays and return the sum.
• Use a pivot table to summarize and aggregate data from multiple sources. For example, if we want to sum the values in both tables by product, we can create a pivot table with the following steps:
  1. Select any cell in one of the tables, and go to the Insert tab, and click PivotTable.
  2. In the Create PivotTable dialog box, check the option to Add this data to the Data Model, and click OK.
  3. In the PivotTable Fields pane, drag the Product field to the Rows area, and the Sales field to the Values area.
  4. In the All Tables pane, select the other table, and drag the Sales field to the Values area again.
  5. In the Values area, right-click on any of the Sales fields, and select Summarize Values By > Sum.
  6. In the Values area, right-click on any of the Sales fields, and select Show Values As > % of Grand Total.
  7. In the PivotTable, you will see the sum of the values in both tables by product, and the percentage of the grand total.