Fix SUMIFS Formulas Working Only in Some Rows in Spreadsheets
Question details
The user needs to fix a SUMIFS formula that calculates correctly for a few rows but returns errors or incorrect results when copied to subsequent rows.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Copying a SUMIFS formula down a column to calculate conditional sums for multiple rows.
- Observed behavior
- The formula works for the initial rows but fails or returns inaccurate results in later rows due to shifting source ranges or merged cell interference.
Identify the exact row where the SUMIFS formula begins returning incorrect results and check if your source data contains any merged cells.
Lock Source Ranges Using Absolute References
Use absolute references (dollar signs) to lock the sum and criteria ranges so they don't shift when you copy the formula down.
When you copy a formula in a spreadsheet, relative cell references automatically adjust. If your source data ranges are not locked, they will shift down, causing the SUMIFS formula to miss the correct data and return wrong results in subsequent rows.
Select the cell containing your first functioning SUMIFS formula and click into the formula bar.
Highlight the sum range (e.g., P2:P10) and press F4 on your keyboard to add dollar signs ($P$2:$P$10). Repeat the F4 shortcut for all criteria ranges (e.g., change O2:O10 to $O$2:$O$10).
Leave the criteria cell references relative (e.g., R2 without dollar signs) so they can dynamically update for each new row.
Press Enter to save the formula, then click and drag the fill handle at the bottom-right of the cell down to copy the corrected formula to the remaining rows.

Convert Data to a Table for Structured References
Formatting your source data as a Table allows you to use column names instead of cell ranges, preventing shifting issues entirely.
Remove Merged Cells that Interfere with Calculations
Merged cells can break formula logic and cause row-level results to evaluate incorrectly.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides intuitive formula auditing tools and robust table management, making it simple to write, debug, and copy complex SUMIFS formulas without reference errors.
- 1. Download and Install: Download WPS Office from the official website and install it on your device.
- 2. Open Your Workbook: Open your existing spreadsheet file in WPS Spreadsheet.
- 3. Toggle Absolute References: Select your formula cell, place your cursor over the range in the formula bar, and press F4 to instantly lock your source ranges.
- 4. Fill Down: Double-click or drag the fill handle to apply the error-free formula across all your rows.

Frequently Asked Questions
What does the F4 key do in a formula?
The F4 key toggles cell references between relative (A1), absolute ($A$1), mixed row (A$1), and mixed column ($A1). Using it on your ranges in a SUMIFS formula prevents them from moving when the formula is copied to other cells.
Why does my SUMIFS formula return a #VALUE! error?
A #VALUE! error in SUMIFS usually occurs if the sum_range and criteria_range arguments are not the exact same size. Ensure both ranges start and end on the exact same rows (e.g., $P$2:$P$10 and $O$2:$O$10).
Can I use GETPIVOTDATA instead of SUMIFS?
Yes. If your data is already summarized in a PivotTable, you can extract specific conditional sums using the GETPIVOTDATA function. This naturally avoids the need for manual range locking, though it does require you to build and maintain a PivotTable.




