Function Problems

Use SUMIF and INDIRECT to Sum Values on a Dynamic Excel Sheet

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Sum values in column D for rows where column A matches a criterion on a dynamically named worksheet.

Use SUMIF and INDIRECT to Sum Values on a Dynamic Excel Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the sheet name

In cell F1, type the exact name of the worksheet to search, such as WK1.

2
Enter the criterion

In cell C2, enter the value to match against column A of the target worksheet.

3
Add the formula

Select an empty result cell and enter =SUMIF(INDIRECT("'"&$F$1&"'!$A:$A"),C2,INDIRECT("'"&$F$1&"'!$D:$D")). Press Enter.

4
Check the result

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 rows with SUMIF and INDIRECT
Keep ranges aligned: For large worksheets, you can replace $A:$A and $D:$D with matching bounded ranges, such as $A$1:$A$1000 and $D$1:$D$1000.
WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
  2. 2. Set the inputs: Enter the target worksheet name in F1 and the value to match in C2.
  3. 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. 4. Review and save: Check the sum against the target sheet, then select Save to keep the formula in your workbook.
Sum matching rows from a dynamically named worksheetOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet functions and formulasWork in a free, easy-to-use office suite
microsoft office alternative - wps office

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.