Formula Errors

How to Fix a SUMIF Formula That Works Only for Some Rows in Excel

Rana GarciaRana Garcia Oct 22, 2026 870 views

Question details

The user needs to correct a formula that accurately returns results for some general-ledger codes but fails to sum others.

How to Fix a SUMIF Formula That Works Only for Some Rows
Product
Spreadsheet
Device & OS
not provided
Scenario
Attempting to sum values across multiple columns based on a criteria range restricted to a single column.
Observed behavior
The SUMIF function processes partial data, returning totals for some rows but ignoring others because the dimensions of the criteria range and sum range do not match.
Before you start

Verify that your cell references extend to the very last row of your dataset; often, formulas stop calculating mid-sheet (e.g., at row 47) when new rows of data have been appended below.

Solution 1Recommended

Use SUMPRODUCT for Mismatched Range Dimensions

Replace the SUMIF function with SUMPRODUCT, which can correctly process multi-column sum ranges against a single-column criteria array.

The standard SUMIF and SUMIFS functions require the sum range and criteria range to be the exact same size. If you try to compare a one-column criteria against a 10-column sum range, the function limits the sum to just the first column.

By leveraging SUMPRODUCT, you force the spreadsheet to evaluate the true/false array of your criteria and multiply it against the entire multi-column grid.

1
Select the target cell

Click on the cell where you want the final calculation for the specific general-ledger code to appear.

2
Enter the SUMPRODUCT formula

Type `=SUMPRODUCT((LEFT(Recurring!C4:C63,5)="83030")*Recurring!G4:S63)`, replacing the ranges and criteria ("83030") with your actual data.

3
Execute the formula

Press Enter. The formula will now assess column C for the criteria and correctly sum the corresponding rows across columns G through S.

Use SUMPRODUCT for Mismatched Range Dimensions
Extracting text with LEFT: The LEFT function in the formula above extracts the first 5 characters of your ledger code. If you are doing exact matches on the entire cell value, you can simply use `(Recurring!C4:C63="83030")` instead.
Advanced Spreadsheets

Effortlessly Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet provides powerful data calculation capabilities, advanced formula auditing, and complete compatibility with complex array formulas to ensure your financial tracking is always accurate.

  1. 1. Open your tracker: Launch WPS Spreadsheet and open your financial tracker workbook.
  2. 2. Audit the formula: Navigate to the 'Formulas' tab and use the 'Error Checking' tool to quickly identify if your criteria and sum ranges are structurally mismatched.
  3. 3. Apply the correction: Use the formula bar to input the suggested SUMPRODUCT array or insert a helper column using the quick SUM shortcut.
  4. 4. Save changes: Click the save icon to preserve your data in the universally compatible .xlsx format.
Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formatsBuilt-in error checking highlights mismatched ranges and missing referencesLightweight program architecture ensures smooth processing of complex SUMPRODUCT arraysFree to download and use with an intuitive tabbed interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIF formula only calculate data from the first column?

When the sum_range parameter spans multiple columns but the criteria_range spans only one, the SUMIF function truncates the sum_range to match the dimensions of the criteria_range. This results in only the first column being calculated.

Can I use SUMIFS instead to solve the multi-column sum range issue?

No. SUMIFS actually has stricter requirements than SUMIF and mandates that all criteria ranges and the sum range have the exact same size and shape. You will receive a #VALUE! error if you attempt it with mismatched dimensions.

How do I ensure my formulas automatically include new rows added later?

You can convert your dataset into a Table by selecting your data and pressing Ctrl+T. Formulas referencing Table columns will automatically expand as you add new rows, preventing calculation cut-offs.

Why did the suggested SUMPRODUCT formula return an error?

SUMPRODUCT will return a #VALUE! error if the arrays contain text values that it is trying to multiply, or if the row lengths of the two arrays (e.g., C4:C63 and G4:S63) are not identical.