Sum Multiple Tables: How to Total Across Sheets

This article explains how to sum values across multiple tables or sheets in a spreadsheet, covering the main methods for Excel, Google Sheets, and similar tools. You will learn the formulas, step-by-step instructions, and common pitfalls to avoid when totaling data from different tabs or ranges.

Formula

The general formula for summing across multiple tables is:

SUM(range1, range2, range3, …)

Where each range is a cell reference, a named range, or a whole sheet reference. For example, =SUM(Sheet1!A1:A10, Sheet2!A1:A10, Sheet3!A1:A10) adds the values in cells A1 through A10 on three different sheets.

In Excel and Google Sheets, the SUM function accepts up to 255 individual arguments. Each argument can be a single cell, a range, or a reference to an entire sheet (like Sheet1!A:A). The function ignores text, logical values (TRUE/FALSE), and empty cells within the ranges.

Worked Example

Suppose you have three monthly sales tables: January, February, and March. Each table lists sales amounts in column B, rows 2 through 10.

To get the total sales for the quarter, you would write:

=SUM(January!B2:B10, February!B2:B10, March!B2:B10)

If January has 500, February has 700, and March has 650, the formula returns 1850. The SUM function simply adds each range’s total together.

Why You Might Need to Sum Across Tables

Spreadsheets often split data into separate sheets or tables for organization—one per month, per department, per product, or per region. When you need a grand total, you can either copy all data into one place (which is error-prone and time-consuming) or use a formula that references multiple tables directly. The latter approach is dynamic: if you update a value in any source table, the total updates automatically.

How to Sum Across Sheets in Excel and Google Sheets

There are three common methods, each suited to different situations.

Method 1: Simple SUM with Multiple Sheet References

This works when the ranges are in the same position on each sheet (e.g., B2:B10 on every sheet).

1. Click the cell where you want the total.
2. Type =SUM(.
3. Click the first sheet tab, then select the range (e.g., B2:B10).
4. Type a comma.
5. Click the next sheet tab and select the same range.
6. Repeat for all sheets, then close the parenthesis and press Enter.

The formula will look like =SUM(Jan!B2:B10, Feb!B2:B10, Mar!B2:B10).

Method 2: 3D Reference (Excel Only)

If your sheets are named sequentially (e.g., Jan, Feb, Mar) and the ranges are identical, you can use a 3D reference:

=SUM(Jan:Mar!B2:B10)

This sums B2:B10 across all sheets from Jan through Mar, inclusive. It’s shorter and automatically includes any sheets placed between those two. However, it requires that the sheets be adjacent in the tab order and that the range is exactly the same on each sheet.

Method 3: Using a List of Sheet Names (Google Sheets)

Google Sheets doesn’t support 3D references directly. Instead, you can use a helper column or a formula that references each sheet individually. For a dynamic approach, you can list sheet names in cells and use SUMPRODUCT with INDIRECT, but that is complex and can slow down large files. The simplest reliable method is to list each sheet reference explicitly in the SUM function.

Summing Across Tables in the Same Sheet

If your “tables� are separate ranges on the same sheet (e.g., Table1 in A1:B10, Table2 in D1:E10), you can sum them directly:

=SUM(A1:B10, D1:E10)

You can also use the SUM function with multiple ranges separated by commas. This works in both Excel and Google Sheets.

Common Mistakes and How to Avoid Them

• Mismatched ranges: If the ranges are different sizes (e.g., B2:B10 on one sheet and B2:B12 on another), the SUM function still works but may include extra or missing cells. Always double-check that each range covers exactly the data you want.
• Including header or total rows: If your source tables have a subtotal row, you might accidentally double-count. Make sure your ranges exclude any existing totals.
• Text or blank cells: SUM ignores text and blanks, but if a cell contains an error (like #DIV/0!), the entire SUM will return an error. Check for errors in source ranges.
• Sheet name with spaces: If a sheet name contains spaces (e.g., “North Regionâ€�), you must wrap it in single quotes in the formula: =SUM('North Region'!B2:B10).
• 3D reference limitations: 3D references work only in Excel, not Google Sheets. Also, they require the same cell range on every sheet—if one sheet has extra rows, the 3D reference won’t include them.

Alternatives to SUM for Complex Scenarios

When you need to sum across tables with conditions (e.g., only sales above $100), you can use SUMIF or SUMIFS across multiple ranges, but these functions don’t work directly across multiple sheets in a single call. Instead, you can add multiple SUMIF results:

=SUMIF(Jan!B:B, ">100", Jan!C:C) + SUMIF(Feb!B:B, ">100", Feb!C:C)

For more advanced scenarios, you might use Power Query (Excel) or Google Sheets’ QUERY function to combine tables first, then sum. These tools are more powerful but require a learning curve.

Frequently Asked Questions

Can I sum multiple tables without typing each reference?

Yes, in Excel you can use the 3D reference method if your sheets are consecutive and the ranges are identical. In Google Sheets, you can use a helper column that lists all sheet names and then use SUMPRODUCT with INDIRECT, but this is more complex and can slow down large files. The most straightforward approach is to click each tab while building the formula—Excel and Google Sheets will insert the correct references for you.

Why is my SUM returning 0?

This usually happens when the cells you’re summing contain text (like numbers stored as text) or are formatted as text. In Excel, you can use the “Text to Columns� feature or the VALUE function to convert them. In Google Sheets, you can use the “Split text to columns� or multiply by 1. Also, check that you haven’t accidentally referenced empty cells.

Does SUM ignore hidden rows or filtered data?

No, the standard SUM function includes hidden rows and filtered-out cells. If you want to sum only visible cells, use the SUBTOTAL function with function number 109 (Excel) or the AGGREGATE function. In Google Sheets, you can use SUBTOTAL as well.

Can I sum across tables in different files?

Yes, you can reference cells from other workbooks by including the file path in the formula, like =SUM([Book1.xlsx]Sheet1!A1:A10, [Book2.xlsx]Sheet1!A1:A10). However, this creates a link between files, and both files must be open for the formula to update. It’s often simpler to consolidate data into one file first.

What is the maximum number of tables I can sum at once?

In Excel, the SUM function accepts up to 255 arguments, so you can reference 255 different ranges or sheets. Google Sheets has a similar limit. In practice, performance may degrade with very large ranges, but for most use cases, this is more than enough.

Is there a way to sum all sheets in a workbook automatically?

In Excel, you can use a 3D reference with the first and last sheet names, but it includes all sheets in between. If you want to sum only specific sheets, you must list them individually. In Google Sheets, there is no built-in “all sheets� reference; you need to list each sheet name in the formula or use a script (Google Apps Script) to generate the formula dynamically.

Related

• Rhetoric Shelves: Mirror of Life?
• How to Root Alcatel One Touch Venture
• Home Assistant Cost: What It Really Takes to Run It

in t | Time (minutes) | min | 50 | 0
in d | Distance (km) | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2