WPS Spreadsheets: Keep SUMIF Criteria and Range References Fixed
Question details
The user needs to fill a SUMIF formula without allowing its criteria range and selected references to shift incorrectly.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A SUMIF formula is being copied down or across a worksheet, and the user wants to control which references remain fixed and which references adjust.
- Observed behavior
- Relative references change when the formula is filled. Without dollar signs, the criteria range can move to the wrong cells.
Identify which parts of the SUMIF formula must stay fixed and which parts should adjust when copied. Select the formula cell and check whether the criteria range, criteria cell, and sum range need absolute or mixed references.
Use absolute and mixed references in the SUMIF formula
Use this method when a filled formula must keep the criteria range fixed while allowing selected rows or columns to adjust.
Dollar signs lock individual parts of a cell reference. In =SUMIF($C$3:$C$95,$C98,E$3:E$95), the criteria range is fully absolute, the criteria cell locks column C but allows its row to change, and the sum range locks its rows while allowing its column to change when copied horizontally.
In WPS Spreadsheets, click the cell containing the SUMIF formula and place the cursor in the formula bar or press F2 to edit it.
Add dollar signs before both the column and row in the criteria range, such as $C$3:$C$95, so this range does not move when the formula is filled.
Use $C98 when the criteria must remain in column C while its row changes as the formula is filled down.
Use E$3:E$95 when the sum range rows should remain fixed while its column can change as the formula is filled across.
Press Enter, select the formula cell, and drag its fill handle down or across, or use Home > Fill to copy the formula into the required cells.
Convert references with the F4 shortcut
Use this option when you want to switch quickly between relative, absolute, and mixed references while editing the formula.
Build Reliable SUMIF Formulas in WPS Spreadsheets
WPS Spreadsheets lets you control absolute and mixed references directly in the formula bar, making it easy to fill SUMIF formulas while preserving criteria ranges and adjusting only the intended rows or columns.
- 1. Open a spreadsheet: Launch WPS Office and open the worksheet containing the SUMIF formula.
- 2. Edit the references: Select the formula cell and use the formula bar to add dollar signs or press F4 to set absolute and mixed references.
- 3. Test the formula: Press Enter and verify the result against the intended criteria and sum ranges.
- 4. Fill the formula: Drag the fill handle or choose Home > Fill to copy the corrected formula across the destination cells.
Frequently Asked Questions
How do I keep a SUMIF criteria range from moving in WPS Spreadsheets?
Add dollar signs to both the column and row references, such as $C$3:$C$95. This makes the criteria range absolute when the formula is filled.
What does $C98 mean in a SUMIF formula?
$C98 is a mixed reference. The column C stays fixed, while row 98 can change when the formula is filled down.
How can I keep the rows in a SUMIF sum range fixed?
Place dollar signs before the row numbers, as in E$3:E$95. The rows remain fixed, while the column can change when the formula is copied across.
Can I use F4 to add dollar signs in WPS Spreadsheets?
Yes. Edit the formula, place the cursor on a reference, and press F4 repeatedly to cycle through relative, absolute, and mixed reference formats.
