How to Fix a SUMIF Formula That Works Only for Some Rows in Excel
Question details
The user needs to correct a formula that accurately returns results for some general-ledger codes but fails to sum others.

- 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.
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.
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.
Click on the cell where you want the final calculation for the specific general-ledger code to appear.
Type `=SUMPRODUCT((LEFT(Recurring!C4:C63,5)="83030")*Recurring!G4:S63)`, replacing the ranges and criteria ("83030") with your actual data.
Press Enter. The formula will now assess column C for the criteria and correctly sum the corresponding rows across columns G through S.

Add a Helper Column for Standard SUMIF
If you prefer sticking to standard SUMIF functions, use a helper column to consolidate your multi-column data into a single sum range first.
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. Open your tracker: Launch WPS Spreadsheet and open your financial tracker workbook.
- 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. Apply the correction: Use the formula bar to input the suggested SUMPRODUCT array or insert a helper column using the quick SUM shortcut.
- 4. Save changes: Click the save icon to preserve your data in the universally compatible .xlsx format.

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.




