Function Problems

How to Copy and Fill a SUMIFS Formula Across an Excel Worksheet

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user needs to know how to properly copy or drag a SUMIFS formula across multiple rows and columns without breaking the cell references.

Product: Excel
Device & OS: not provided
Scenario: Dragging and filling a SUMIFS formula across a worksheet to aggregate data dynamically based on multiple criteria.
Observed behavior: Formula references shift incorrectly to unintended cells if absolute and relative references are not configured correctly before filling.

Before you start

Before dragging your formula, identify exactly which rows or columns contain your criteria and sum ranges, as you will need to lock them using the F4 key to prevent them from shifting.

Solution 1Recommended

Use Absolute and Mixed References to Lock Ranges and Criteria

Apply the correct reference types (using the $ symbol) to ensure your criteria columns and rows stay fixed while filling across the worksheet.

When you drag a formula across cells, Excel automatically adjusts the cell references relative to the new cell's position. To prevent your sum range and criteria ranges from moving, you must lock them using absolute references (e.g., Table2!$G:$G).

For your specific criteria arguments, you can use mixed references to lock either the row or the column, depending on the direction you are dragging the formula.

Select the target cell

Click on the cell where you want to write your initial SUMIFS formula.

Enter the SUMIFS formula with locked ranges

Start typing your formula. Specify your sum range and criteria ranges using absolute references (e.g., `=SUMIFS(Table2!$G:$G, Table2!$B:$B, ...)`).

Apply mixed references to criteria cells

For criteria that should stay in the same column when dragged right, lock the column (e.g., `$A22`). For criteria that should stay in the same row when dragged down, lock the row (e.g., `D$2`). You can press the F4 key to easily toggle through these reference types.

Fill the formula across cells

Press Enter to calculate the first cell. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) across your desired rows and columns.

Use Absolute and Mixed References to Lock Ranges and Criteria

Understanding the F4 Key: Pressing F4 while the cursor is on a cell reference in the formula bar will cycle through absolute ($A$1), mixed row (A$1), mixed column ($A1), and relative (A1) references.

Efficient Data Calculation

Use WPS Spreadsheet to Easily Manage SUMIFS Formulas

WPS Spreadsheet provides a highly compatible and intuitive interface for handling complex data calculations. You can easily manage absolute and relative references using the F4 key, making data aggregation across large tables seamless.

1. Open your workbook: Launch WPS Spreadsheet and open your existing data file.
2. Input the formula: Select a cell and type your SUMIFS function. Use the F4 key to quickly lock your data ranges with the '$' symbol.
3. Drag to fill: Click the bottom-right corner of the selected cell and drag it across your table to instantly fill the formula with the correct references.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive formula bar with helpful syntax tooltips for advanced functions like SUMIFS.Lightweight, fast, and completely free to use for daily data analysis tasks.

Frequently Asked Questions

Why is my SUMIFS formula returning 0 when I copy it to other cells?

This usually happens because the criteria ranges or the sum range were not locked with absolute references (like $A$1:$A$10). When you copy the formula, the ranges shift to empty cells. Update the original formula with absolute references and drag it again.

What is the difference between $A1 and A$1 in a formula?

$A1 locks the column, meaning the column 'A' will not change if you drag the formula left or right, but the row number will change if dragged up or down. A$1 locks the row, keeping row '1' fixed regardless of vertical copying, while the column letter changes when dragged horizontally.

Can I use named ranges in SUMIFS to avoid dealing with absolute references?

Yes. By defining a name for your sum and criteria ranges (e.g., 'SalesData'), you can use these names directly in your SUMIFS formula. Named ranges act as absolute references by default, making your formula much easier to read and copy.

Excel & Data Problems