Function Problems

Sum Sales by Product and Year Across Stacked Excel Tables

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Calculate sales totals by product and year from multiple vertically stacked tables and improve the data layout.

Sum Sales by Product and Year Across Stacked Excel Tables
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.
Before you start

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.

Solution 1Recommended

Consolidate the tables and use SUMIFS

Use this approach for a maintainable summary when you can combine the records into one consistent table.

1
Create a consolidated list

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.

2
Set up summary criteria

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.

3
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 last row to include all records.

4
Summarize other criteria

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.

Consolidate the tables and use SUMIFS
Why use a year range?: The formula includes dates from January 1 of the selected year up to, but not including, January 1 of the next year. It also handles dates that contain times.
Calculate sales totals in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 3. Add summary criteria: Enter the product name in F2 and a four-digit year in G2.
  4. 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.
Calculate product-by-year sales totals with SUMIFSOpen and edit Microsoft Excel XLSX and XLS filesKeep consolidated sales records in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.