Sum Sales by Product and Year Across Stacked Excel Tables
Question details
Calculate sales totals by product and year from multiple vertically stacked tables and improve the data layout.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sales records are stored in separate tables stacked on one sheet, and project dates determine which year each sale belongs to.
- Observed behavior
- The desired result is a product-by-year summary that is easier to calculate and maintain.
Identify the product, project, date, and sales amount fields in each table. Check that dates are Excel date values and sales amounts are numeric, rather than text.
Consolidate the tables and use SUMIFS
Use this approach for a maintainable summary when you can combine the records into one consistent table.
On a new sheet, enter Product, Project, Date, and Sales in cells A1:D1. Copy the records from each stacked table beneath these headers, with one row per product, project, date, and sales amount.
Enter a product name in F2 and a four-digit year, such as 2025, in G2. The product name must match the consolidated Product column.
In H2, enter =SUMIFS($D$2:$D$1000,$A$2:$A$1000,$F2,$C$2:$C$1000,">="&DATE(G2,1,1),$C$2:$C$1000,"<"&DATE(G2+1,1,1)). Adjust the last row to include all records.
Enter additional product and year pairs in columns F and G, then copy the formula in H2 down. To summarize by project as well, add a Project criteria range and matching project cell to SUMIFS.

Use SUMPRODUCT on a consolidated sales list
Use SUMPRODUCT as an alternative when you want to combine product and date conditions in one calculation.
Summarize product sales by year in WPS Office
Open your workbook in WPS Spreadsheets, consolidate the sales records, and use SUMIFS to calculate totals by product and project date.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Consolidate the records: On a new sheet, place Product, Project, Date, and Sales in columns A through D, then copy each table's records below the headers.
- 3. Add summary criteria: Enter the product name in F2 and a four-digit year in G2.
- 4. Calculate the total: In H2, enter =SUMIFS($D$2:$D$1000,$A$2:$A$1000,$F2,$C$2:$C$1000,">="&DATE(G2,1,1),$C$2:$C$1000,"<"&DATE(G2+1,1,1)). Adjust the ranges to cover your records.

Frequently Asked Questions
Why combine the stacked tables into one list?
A single list with consistent Product, Project, Date, and Sales columns lets SUMIFS, SUMPRODUCT, and PivotTables use the same ranges without a separate formula for each block.
Can I summarize the consolidated data with a PivotTable?
Yes. Select the consolidated list and choose Insert > PivotTable. Place Product in Rows, Date in Columns, and Sales in Values, then group the dates by year if needed.
Why does my year formula return zero?
Check that the product in F2 matches the Product column, that G2 contains a four-digit year, and that the date and sales cells contain real dates and numbers rather than text. Also make sure the formula ranges include all records.
Can FILTER work with the original stacked tables?
It can, but the formula depends on the position of each block's headers, dates, and sales values. FILTER and CHOOSECOLS may help select matching blocks in Excel versions that support them; a consolidated list is usually easier to maintain.




