Function Problems

Use INDIRECT with SUMIFS for a Dynamic Excel Sum Range

Chanuka GeekiyanageChanuka Geekiyanage Nov 20, 2026 870 views

Question details

Create a SUMIFS formula that selects its sum column using a column reference stored in another cell.

Use INDIRECT with SUMIFS for a Dynamic Excel Sum Range
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to sum values on the SOFT16 sheet while choosing the sum column dynamically and matching a criterion in column J.
Observed behavior
The formula should update its sum range when the column letter in C3 changes and sum rows where column J matches D3.
Before you start

Confirm that the SOFT16 sheet exists, column J contains the values to match, and the column you want to sum contains numbers. In this example, enter the column letter in C3 and the matching criterion in D3 on the sheet where you will enter the formula.

Solution 1Recommended

Select the SUMIFS sum column with INDIRECT

Use this formula when a cell contains the letter of the column you want to sum on SOFT16.

1
Enter the column letter

On the formula sheet, select C3 and enter a column letter such as AC, without a colon or sheet name.

2
Enter the criterion

Select D3 and enter the value to match against column J on the SOFT16 sheet.

3
Enter the formula

Select an empty cell and enter =SUMIFS(INDIRECT("SOFT16!"&C3&":"&C3),SOFT16!J:J,D3). Press Enter to calculate the sum.

4
Change the sum column

Replace AC in C3 with another valid column letter to sum that column instead. The formula continues to match column J against D3.

Performance with large workbooks: INDIRECT is volatile, and full-column references can slow calculation. If needed, use matching bounded ranges, such as INDIRECT("SOFT16!"&C3&"2:"&C3&"1000") and SOFT16!J2:J1000.
Work with dynamic spreadsheet formulas

Build a dynamic SUMIFS formula in WPS Spreadsheets

Use INDIRECT and SUMIFS in WPS Spreadsheets to select a sum column from a cell and match criteria on another sheet.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook. Confirm that it contains the SOFT16 sheet.
  2. 2. Set the inputs: On the sheet where you will enter the formula, type AC in C3 and the value to match in D3.
  3. 3. Enter the formula: Select an empty cell and enter =SUMIFS(INDIRECT("SOFT16!"&C3&":"&C3),SOFT16!J:J,D3), then press Enter.
  4. 4. Update the column: Edit C3 to another column letter when you want to sum a different column on SOFT16.
Change the sum column by editing one cell.Use SUMIFS to match values in a separate criteria column.Open and edit Microsoft Excel XLSX and XLS files.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

What should I enter in C3 to sum column AC?

Enter AC in C3 without a colon or sheet name. INDIRECT combines it with SOFT16! to reference SOFT16!AC:AC.

How do I change the matching criterion?

Edit D3 on the formula sheet. SUMIFS compares its value with each corresponding cell in column J on SOFT16.

Why does the formula return a reference error?

Check that C3 contains a valid column letter and that the sheet is named SOFT16. For a sheet name with spaces, enclose the name in single quotes inside the reference text, such as INDIRECT("'Sales Data'!"&C3&":"&C3).

Can I limit the formula to specific rows?

Yes. Use ranges of the same size for the sum and criteria ranges. For rows 2 through 1000, use =SUMIFS(INDIRECT("SOFT16!"&C3&"2:"&C3&"1000"),SOFT16!J2:J1000,D3).