Formula Errors

How to Use Calculated Row Numbers in an Excel SUM Formula

Khadija Khan Oct 9, 2026 869 views

Question details

The user wants to sum a dynamic range in Excel by combining the SUM function with row numbers calculated or stored in specific cells.

How to Use Calculated Row Numbers in an Excel SUM Formula

Product: Excel
Device & OS: not provided
Scenario: Building a dynamic data range for a formula where the starting and ending row limits are stored as numeric values in separate cells.
Observed behavior: The formula needs to correctly parse the cell values as row numbers to output a valid SUM, while avoiding common #REF! syntax errors when referencing different sheets.

Before you start

Ensure the cells containing your starting and ending row numbers contain valid numeric values, and verify the exact name of the target worksheet to avoid reference errors.

Solution 1Recommended

Use INDIRECT for a Dynamic Range in the Same Sheet

Use the INDIRECT function to convert text strings and cell references into a valid range for the SUM formula.

By combining text strings (like the column letter) with cell references containing row numbers using the ampersand (&), the INDIRECT function translates the resulting text into a valid Excel range that SUM can calculate.

Enter your row numbers

Type your starting row number into cell A1 and your ending row number into cell A2.

Input the dynamic formula

Select the cell where you want the total to appear and type =SUM(INDIRECT("C"&A1&":C"&A2)). Replace 'C' with your actual target column.

Execute the calculation

Press Enter. Excel will automatically sum the values in column C between the row numbers specified in A1 and A2.

Use INDIRECT for a Dynamic Range in the Same Sheet

Solution 2

Reference Calculated Row Numbers in Another Worksheet

Use this method if the data you want to sum is located on a different worksheet than your formula.

Dynamic Formulas in WPS Office

Easily Calculate Dynamic Ranges with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for dynamic array functions, including SUM and INDIRECT, allowing you to manipulate and calculate variable data ranges effortlessly without complex adjustments.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
2. Define your row variables: Input your desired starting and ending row numbers into designated cells, such as A1 and A2.
3. Apply the INDIRECT function: Enter =SUM(INDIRECT("C"&A1&":C"&A2)) in your target cell to instantly calculate the dynamic range.

Fully compatible with Microsoft Excel formulas, including INDIRECT and SUM across multiple sheets.Lightweight architecture ensures fast calculation even with heavy dynamic arrays and large datasets.Free to use with a familiar, highly intuitive spreadsheet interface.

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error?

A #REF! error typically occurs if the cell references are invalid, if the row numbers in your reference cells are missing or formatted as text, or if the single quotation marks around a worksheet name are placed incorrectly (e.g., typing 'Sheet1!' instead of 'Sheet1'!).

Can I use dynamic columns with INDIRECT instead of just rows?

Yes, you can make both rows and columns dynamic. You can concatenate column letters stored in cells alongside your row numbers, or use the ADDRESS function combined with INDIRECT to convert dynamic row and column numbers into a valid cell reference.

Will INDIRECT update automatically if I insert or delete rows?

Because INDIRECT evaluates a text string (like "C"&A1), hardcoded elements like the column "C" will not automatically shift if columns are inserted. However, if the cells dictating your row numbers (A1 and A2) update their values, the INDIRECT range will immediately recalculate.

Excel & Data Problems