Use INDIRECT with SUMIFS for a Dynamic Excel Sum Range
Question details
Create a SUMIFS formula that selects its sum column using a column reference stored in another cell.

- 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.
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.
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.
On the formula sheet, select C3 and enter a column letter such as AC, without a colon or sheet name.
Select D3 and enter the value to match against column J on the SOFT16 sheet.
Select an empty cell and enter =SUMIFS(INDIRECT("SOFT16!"&C3&":"&C3),SOFT16!J:J,D3). Press Enter to calculate the sum.
Replace AC in C3 with another valid column letter to sum that column instead. The formula continues to match column J against D3.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook. Confirm that it contains the SOFT16 sheet.
- 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. Enter the formula: Select an empty cell and enter =SUMIFS(INDIRECT("SOFT16!"&C3&":"&C3),SOFT16!J:J,D3), then press Enter.
- 4. Update the column: Edit C3 to another column letter when you want to sum a different column on SOFT16.

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




