Use SUMIF and INDIRECT to Sum Values on a Dynamic Excel Sheet
Question details
Sum values in column D for rows where column A matches a criterion on a dynamically named worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet name is stored in a cell, and the user wants to sum corresponding values instead of counting matches.
- Observed behavior
- The desired formula should use the worksheet name to find matches in column A and add the corresponding values in column D.
Enter the target worksheet name in F1 and the value to match in C2. Check that the target sheet contains the criteria in column A and the numbers to sum in the corresponding rows of column D.
Sum matching rows with SUMIF and INDIRECT
Use this formula when the worksheet to search is named in F1 and the matching value is in C2.
INDIRECT converts the sheet name in F1 into range references. SUMIF checks column A against C2 and sums the corresponding cells in column D.
In cell F1, type the exact name of the worksheet to search, such as WK1.
In cell C2, enter the value to match against column A of the target worksheet.
Select an empty result cell and enter =SUMIF(INDIRECT("'"&$F$1&"'!$A:$A"),C2,INDIRECT("'"&$F$1&"'!$D:$D")). Press Enter.
Compare the result with the values in column D on the sheet named in F1. Change F1 to another worksheet name to sum matches on that sheet.

Sum matching values across dynamic sheets in WPS Spreadsheets
Use SUMIF with INDIRECT in WPS Spreadsheets to search a worksheet named in a cell and sum its matching values.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Set the inputs: Enter the target worksheet name in F1 and the value to match in C2.
- 3. Enter the formula: Select an empty cell and enter =SUMIF(INDIRECT("'"&$F$1&"'!$A:$A"),C2,INDIRECT("'"&$F$1&"'!$D:$D")). Press Enter.
- 4. Review and save: Check the sum against the target sheet, then select Save to keep the formula in your workbook.

Frequently Asked Questions
Why does the formula return #REF!?
Check that F1 contains the exact name of an existing worksheet. If the sheet name contains an apostrophe, use SUBSTITUTE($F$1,"'","''") in place of $F$1 in both INDIRECT references.
Can I use a fixed worksheet name instead of INDIRECT?
Yes. For a sheet named WK1, enter =SUMIF('WK1'!$A$1:$A$99,C2,'WK1'!$D$1:$D$99). Change the sheet name in both ranges if you need to search another sheet.
Why is the result zero when I expect a sum?
Check that C2 matches values in column A and that the corresponding cells in column D contain numbers rather than text. Also verify that F1 names the intended worksheet.
Can I copy the formula down for other criteria?
Yes. Drag the formula cell's fill handle down. The reference to C2 will change for each row, while $F$1 stays fixed on the worksheet name.




