Sum Rows by Matching Month, Year, and Code in Excel
Question details
Create a summary that totals quantities and prices for records sharing the same month, year, and code.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains thousands of records, including duplicate month, year, and code combinations.
- Observed behavior
- The desired summary has one row per month, year, and code combination, with corresponding quantity and price totals.
Identify the columns containing month, year, code, quantity, and price. Make sure matching months and years use consistent values and that quantities and prices are stored as numbers.
Use SUMIFS to total each matching combination in Excel
Use a summary table and one fillable formula to total quantities and prices for each month, year, and code.
This example assumes source columns A through E contain month, year, quantity, price, and code, respectively. Adjust the column references and row limits to match your data.
In columns G through K, enter headers for Month, Year, Code, Quantity Total, and Price Total. Copy the source month, year, and code values into columns G, H, and I.
Select the summary columns G through I, including their headers. Choose Data > Remove Duplicates, select all three columns, and confirm to keep one row for each combination.
In J2, enter =SUMIFS(C$2:C$5,$A$2:$A$5,$G2,$B$2:$B$5,$H2,$E$2:$E$5,$I2). Replace row 5 with the last source row so every record is included.
Drag the fill handle from J2 across to K2, then down through the summary rows. The sum range shifts from column C to column D, while the matching criteria remain fixed to the correct source columns.

Total matching records with SUMIFS in WPS Spreadsheets
Use WPS Spreadsheets to build a summary of quantities and prices by month, year, and code with the same SUMIFS approach.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
- 2. Prepare the summary: Copy the month, year, and code columns into adjacent summary columns. Select them and use Data > Remove Duplicates to retain unique combinations.
- 3. Enter SUMIFS: In the first total cell, enter a SUMIFS formula using the quantity range and the three matching criteria. For the example layout, use =SUMIFS(C$2:C$5,$A$2:$A$5,$G2,$B$2:$B$5,$H2,$E$2:$E$5,$I2), adjusting the final row as needed.
- 4. Fill the totals: Drag the formula's fill handle across to the price total column and down through the summary rows.

Frequently Asked Questions
How do I include thousands of source rows in SUMIFS?
Extend every source range in the formula to the last data row, such as C$2:C$5000 and $A$2:$A$5000. All SUMIFS ranges must cover the same number of rows.
How can I summarize records when the month and year are in a date column?
Add helper columns using =MONTH(A2) and =YEAR(A2), where A2 contains the date. Fill them down, then use those helper columns as the month and year criteria ranges in SUMIFS.
Why is SUMIFS returning zero for a matching code?
Check that the summary code matches the source code exactly and that both use compatible text or number formats. Also verify that the sum and criteria ranges cover the intended rows.
Can I use a PivotTable instead of SUMIFS?
Yes. Select the source data and choose Insert > PivotTable. Add month, year, and code to Rows, then add quantity and price to Values and set both value fields to Sum.




