Formula Errors

Fix Excel SUMIF Results When Criteria and Sum Ranges Differ in Size

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

Match the SUMIF Criteria and Sum Range Dimensions

Use this approach when one criteria column corresponds to one column of values.

1
Inspect the formula

Select the cell containing the SUMIF formula and check its arguments in the formula bar.

2
Align the ranges

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).

3
Verify the total

Press Enter, then compare the result with the values in column C whose corresponding cells in column A contain SOMETEXT.

How Excel handles a mismatch: Excel uses the top-left cell of the supplied sum range as a starting point and evaluates a range with the dimensions of the criteria range. This can include cells outside the sum range you specified.
Work with spreadsheets in WPS Office

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. 1. Open the workbook: Launch WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select the formula: Click the cell with the unexpected total and inspect its SUMIF arguments in the formula bar.
  3. 3. Correct the ranges: Edit the formula to use matching dimensions, such as =SUMIF(A1:A5,"SOMETEXT",C1:C5), and press Enter.
  4. 4. Review and save: Compare the total with the matching rows, then choose Save to keep the corrected formula.
Edit SUMIF formulas and inspect matching ranges in the formula bar.Open and save Microsoft Excel XLSX files.Use a familiar spreadsheet interface to review and verify totals.
microsoft office alternative - wps office

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.