Function Problems

Sum Values Across Multiple Excel Tables by Month and Year

Elise WilliamsElise Williams Nov 20, 2026 868 views

Question details

Calculate a total across multiple Excel tables using month and year criteria.

Sum Values Across Multiple Excel Tables by Month and Year
Product
Microsoft Excel
Device & OS
not provided
Scenario
Several structured tables contain matching Month, Year, and value columns, and the user wants a combined total without writing a separate SUMIFS expression for each table.
Observed behavior
A structured reference such as Table1:Table32[Value 1] cannot be used as a direct SUMIFS range.
Before you start

Check that each table has the same Month, Year, and value column names. Identify the exact table names under Table Design so you can enter them on a summary sheet.

Solution 1Recommended

Use INDIRECT and SUMIFS for each table, then add the results

Use a list of table names to calculate subtotals without adding formulas to the existing data sheets.

INDIRECT builds a structured reference from each table name. SUMIFS calculates one subtotal per table, and a separate SUM formula combines those subtotals.

This example assumes Month contains text such as Jan, Year contains 2017, and the value column is named Value 1.

1
List the table names

On a summary worksheet, enter the exact table names, such as Table1 through Table32, in cells A2:A33.

2
Calculate the first subtotal

In cell B2, enter =SUMIFS(INDIRECT(A2&"[Value 1]"),INDIRECT(A2&"[Month]"),"Jan",INDIRECT(A2&"[Year]"),2017). Change the column names and criteria to match your data.

3
Fill the formula down

Drag the fill handle from B2 down to B33, or double-click it, to calculate a subtotal for each listed table.

4
Add the subtotals

In an empty cell, enter =SUM(B2:B33) to get the combined total. Adjust the range if your table-name list is a different length.

Use INDIRECT and SUMIFS for each table, then add the results
Consider workbook performance: INDIRECT is volatile and can slow larger workbooks. For a large or frequently refreshed dataset, consider consolidating the tables with Power Query.
Calculate totals in WPS Spreadsheets

Sum consolidated data by month and year in WPS Spreadsheets

Open your workbook in WPS Spreadsheets, combine the relevant rows on a summary sheet, and use SUMIFS to calculate the total for a month and year.

  1. 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
  2. 2. Create a consolidated sheet: Add a worksheet and enter Month, Year, and Value 1 as headers in cells A1:C1.
  3. 3. Copy the data rows: Copy the corresponding Month, Year, and Value 1 values from each source table and paste them beneath the headers, keeping the columns aligned.
  4. 4. Calculate the total: In an empty cell, enter =SUMIFS(C2:C1000,A2:A1000,"Jan",B2:B1000,2017). Adjust the ranges to include all pasted rows and change the criteria as needed.
Calculate month-and-year totals with SUMIFSOpen and save Microsoft Excel XLSX filesWork with familiar spreadsheet formulas and worksheetsUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Can SUMIFS reference Table1 through Table32 directly?

No. A reference such as Table1:Table32[Value 1] is not a valid way to span structured tables in SUMIFS. Calculate a subtotal for each table or consolidate their rows first.

Can I change the month and year without editing every formula?

Yes. Enter the month in E1 and the year in F1, then replace "Jan" and 2017 in the SUMIFS formula with $E$1 and $F$1 before filling it down.

Why does the INDIRECT formula return a reference error?

Check that the name in column A exactly matches an existing table name and that the structured column names match the table headers. You can check a table's name by selecting it and opening Table Design.

Will the Power Query total update when source rows change?

After changing the source tables, choose Data > Refresh All to refresh the appended query. The SUMIFS formula referencing the loaded table can then use the refreshed data.