Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Understanding Pivot Tables in Excel
  • Excel Line Chart
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to create a Histogram in Excel
  • Conditional Formatting Color Scales Examples in Excel

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Two-way lookup with VLOOKUP in Excel
  • Lookup entire row in Excel
  • How to use Excel TRANSPOSE Function
  • Count unique text values with criteria

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Excel Data validation date in next 30 days
  • How To Create Drop-down List in Excel

How to sum a total in multiple Excel tables

by

To sum a total in multiple tables, you can use the SUM function and structured references to refer to the columns to sum. See example below:

Formula

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

Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error.

Explanation

In the example shown, the formula in I6 is:

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

How this formula works

This formula uses structured references to refer to the “Amount” column in each table. The structured references in this formula resolve to normal references like this:

=SUM(Table1[Amount],Table2[Amount])
=SUM(C7:C11,F7:F13)
=1495.5

When rows or columns are added or removed from either table, the formula will continue to return correct results. In addition, the formula will work even if the tables are located on different sheets in a workbook.

Worked Example:   Sum range with INDEX in Excel

Alternative syntax with Total row

It is also possible to reference the total row in a table directly, as long as tables have the Total Row enabled. The syntax looks like this:

Table1[[#Totals],[Amount]]

Translated: “The value for Amount in the Total row of Table1”.

Worked Example:   Get column name from index in Excel Table

Using this syntax, the original formula above could be re-written like this:

=SUM(Table1[[#Totals],[Amount]],Table2[[#Totals],[Amount]])

As above, this formula will work even when the table is moved or resized.

Worked Example:   Calculate Conditional Percentile 'IF' in table in Excel

Post navigation

Previous Post:

How to conditionally sum numeric data in an Excel table using SUMIFS

Next Post:

Create hyperlink with VLOOKUP in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Extract multiple matches into separate rows in Excel
  • How to use Excel XOR Function
  • Nested IF function example in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to get Weekdays, Working days between Two Dates in Excel
  • Get date from day number in Excel
  • Convert date string to date time in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel

General

  • Count cells less than in Excel
  • Find, Select, Replace and Go To Special in Excel
  • How to get Excel workbook path only
  • How to make excel worksheets print on one page?
  • 231 Keyboard Shortcut Keys In Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning