Calculation Issues

Sum Rows by Matching Month, Year, and Code in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Create a summary that totals quantities and prices for records sharing the same month, year, and code.

Sum Rows by Matching Month, Year, and Code in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the summary columns

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.

2
Keep unique combinations

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.

3
Enter the formula

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.

4
Fill across and down

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.

Use SUMIFS to total each matching combination in Excel
Check what the price column represents: The formula adds the values in the price column. If those values are unit prices and you need sales value, first calculate quantity multiplied by unit price in a helper column, then sum that column instead.
Summarize spreadsheet records

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 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. 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. 4. Fill the totals: Drag the formula's fill handle across to the price total column and down through the summary rows.
Total records using multiple matching criteriaOpen and edit Microsoft Excel XLSX and XLS filesFill one formula across and down a summary tableUse a familiar spreadsheet interface for large worksheets
microsoft office alternative - wps office

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.