Fix Excel SUMIF Results When Criteria and Sum Ranges Differ in Size
Question details
The user needs to correct unexpected SUMIF totals when the criteria range and sum range have different sizes or shapes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A SUMIF formula checks criteria in one or more columns and sums corresponding values from another range.
- Observed behavior
- The formula returns unexpected values because the criteria range and sum range do not have matching dimensions.
Identify the cells containing the criteria and the cells containing the values to sum. Check whether the ranges cover the same number of rows and columns and start on corresponding rows.
Match the SUMIF Criteria and Sum Range Dimensions
Use this approach when one criteria column corresponds to one column of values.
Select the cell containing the SUMIF formula and check its arguments in the formula bar.
Edit the formula so the criteria range and sum range have the same size and shape. For example, use =SUMIF(A1:A5,"SOMETEXT",C1:C5).
Press Enter, then compare the result with the values in column C whose corresponding cells in column A contain SOMETEXT.
Add Separate SUMIF Results for Multiple Criteria Columns
Use separate SUMIF functions when each criteria column should contribute its own total.
Correct SUMIF Range Sizes in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and edit the SUMIF formula so its criteria and sum ranges align.
- 1. Open the workbook: Launch WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Select the formula: Click the cell with the unexpected total and inspect its SUMIF arguments in the formula bar.
- 3. Correct the ranges: Edit the formula to use matching dimensions, such as =SUMIF(A1:A5,"SOMETEXT",C1:C5), and press Enter.
- 4. Review and save: Compare the total with the matching rows, then choose Save to keep the corrected formula.

Frequently Asked Questions
Why does SUMIF include cells outside my specified sum range?
Excel uses the top-left cell of the sum range as a starting point and applies the dimensions of the criteria range. Edit the formula so both ranges have the same size and shape.
Can I check two criteria columns and sum one value column?
Yes. Add separate SUMIF results, such as =SUM(SUMIF(A1:A5,"SOMETEXT",C1:C5),SUMIF(B1:B5,"SOMETEXT",C1:C5)). A row matching in both columns will be counted twice.
Do the criteria and sum ranges have to start on the same row?
They should start on corresponding rows for the records you want to compare. For example, A1:A5 and C1:C5 pair each criterion with the value on the same row.
Can I fix the same formula in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets, select the formula cell, and use the formula bar to make the criteria and sum ranges the same size and shape.




