Use Excel SUMIFS for Two Matching Row and Column Conditions
Question details
Create a SUMIFS formula that adds values only when two criteria, such as a row condition and a column condition, both match.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to total values in column C for records whose values in columns A and B match the criteria listed in columns A and B of a summary row.
- Observed behavior
- The desired result is a separate total for each criteria pair, with the formula filled down through the summary list.
Confirm that the sum range and every criteria range cover the same number of rows. Check that the criteria values in the source data and summary rows use matching text, numbers, and date formats.
Use SUMIFS with two criteria ranges and fill the formula down
Use this method when each total depends on an exact match in both the first and second criteria columns.
SUMIFS evaluates every criteria pair together. It adds a value from column C only when the value in column A matches the first criterion and the value in column B matches the second criterion.
Click cell C44, or select the first cell where the two-condition total should appear.
Type =SUMIFS($C$2:$C$30,$A$2:$A$30,A44,$B$2:$B$30,B44) and press Enter. Column C is the sum range, while columns A and B are the criteria ranges.
Make sure $C$2:$C$30, $A$2:$A$30, and $B$2:$B$30 start and end on the same rows. SUMIFS returns an error or incorrect result when the paired ranges are not aligned.
Select C44 and drag the fill handle downward, or copy C44 and paste it into the remaining total cells. The fixed source ranges stay unchanged while A44 and B44 adjust for each row.
Calculate Two-Criteria Totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets lets you build the same two-condition SUMIFS formula, copy it down a report, and work with Excel-compatible workbook formats. Enter the formula once, then use the fill handle to calculate totals for every criteria pair.
- 1. Open the worksheet in WPS Spreadsheets: Open the workbook and select the worksheet containing the source columns and the summary criteria rows.
- 2. Choose the first summary total cell: Click cell C44, or select the first output cell beside the criteria values.
- 3. Enter the SUMIFS formula: In the formula bar, enter =SUMIFS($C$2:$C$30,$A$2:$A$30,A44,$B$2:$B$30,B44), then press Enter.
- 4. Fill the formula through the report: Select the completed cell and drag its fill handle down to calculate each remaining pair of criteria.
Frequently Asked Questions
How does SUMIFS apply two matching conditions in Excel?
SUMIFS checks each criteria range on the same source row. In =SUMIFS(C2:C30,A2:A30,A44,B2:B30,B44), Excel adds a value from column C only when column A equals A44 and column B equals B44.
Why must SUMIFS ranges be the same size?
Excel compares positions across the sum range and criteria ranges row by row. Use matching ranges such as C2:C30, A2:A30, and B2:B30; do not pair C2:C30 with A2:A29.
How do I copy a SUMIFS formula down without changing the data ranges?
Use dollar signs to lock the source ranges, such as $C$2:$C$30, $A$2:$A$30, and $B$2:$B$30. Then drag the fill handle down so the criteria references A44 and B44 change to the next row.
Can SUMIFS match text and dates as two criteria?
Yes. Put the text criterion in one criteria cell and the date criterion in another, then reference both cells in SUMIFS. Ensure source dates are real date values rather than text strings.
