Formula Errors

Fix SUMIFS Formulas Working Only in Some Rows in Spreadsheets

Ayan MasoodAyan Masood Nov 10, 2026 869 views

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.

How to Fix SUMIFS Formulas That Only Work in Some 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.
Before you start

Identify the exact row where the SUMIFS formula begins returning incorrect results and check if your source data contains any merged cells.

Solution 1Recommended

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.

1
Edit the initial formula

Select the cell containing your first functioning SUMIFS formula and click into the formula bar.

2
Apply absolute references to ranges

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

3
Keep criteria cells relative

Leave the criteria cell references relative (e.g., R2 without dollar signs) so they can dynamically update for each new row.

4
Copy the locked formula

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.

Lock Source Ranges Using Absolute References
Formula structure: Your final locked formula should look similar to: =SUMIFS($P$2:$P$10, $O$2:$O$10, R2, $N$2:$N$10, S2).
Free Office Suite

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. 1. Download and Install: Download WPS Office from the official website and install it on your device.
  2. 2. Open Your Workbook: Open your existing spreadsheet file in WPS Spreadsheet.
  3. 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. 4. Fill Down: Double-click or drag the fill handle to apply the error-free formula across all your rows.
Fully compatible with Microsoft Excel formulas, functions, and structured table references.Built-in Error Checking and Evaluate Formula tools for quick debugging of shifting ranges.Seamlessly supports absolute references, keyboard shortcuts, and pivot tables.Free, lightweight, and available on Windows, Mac, Linux, iOS, and Android.
microsoft office alternative - wps office

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.