Sum Values Across Multiple Excel Tables by Month and Year
Question details
Calculate a total across multiple Excel tables using month and year criteria.

- 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.
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.
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.
On a summary worksheet, enter the exact table names, such as Table1 through Table32, in cells A2:A33.
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.
Drag the fill handle from B2 down to B33, or double-click it, to calculate a subtotal for each listed table.
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.

Consolidate the tables with Power Query
Use this approach when you have many tables or want a consolidated source that can be refreshed.
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. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
- 2. Create a consolidated sheet: Add a worksheet and enter Month, Year, and Value 1 as headers in cells A1:C1.
- 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. 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.

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.




