How to Use SUMPRODUCT, SUMIF, and INDIRECT Across Multiple Excel Sheets
Question details
The user wants to sum matching employee values across multiple worksheets dynamically using a single formula instead of manually adding a formula for every sheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating conditional sums across multiple worksheets based on specific criteria using a master summary sheet.
- Observed behavior
- The user needs to construct a nested formula using SUMPRODUCT, SUMIF, and INDIRECT, ensuring proper syntax for worksheet references that might contain spaces.
Create a list of all your worksheet names in a specific column on your summary sheet to easily reference them with the INDIRECT function. Ensure the worksheet names in your list match the actual sheet tabs exactly.
Combine SUMPRODUCT, SUMIF, and INDIRECT for 3D Summing
Use a nested formula to dynamically search and sum values across listed worksheets without manually linking each sheet.
By combining SUMPRODUCT, SUMIF, and INDIRECT, you can create a powerful array formula that evaluates criteria across multiple sheets simultaneously. The INDIRECT function converts your list of sheet names into actual references, SUMIF calculates the conditional sum for each sheet, and SUMPRODUCT adds the results together.
In your summary sheet, create a list of the exact sheet names you want to sum across. For example, type the sheet names in cells G2 and G3.
Inside the SUMIF function, use INDIRECT to reference the sheet names. If your criteria range is B4:B1000, write INDIRECT("'"&$G$2:$G$3&"'!B4:B1000"). Note the single quotes inside the double quotes to handle sheet names with spaces.
Complete the SUMIF by adding your search criterion (e.g., cell A2) and the sum range. The nested SUMIF should look like SUMIF(INDIRECT("'"&$G$2:$G$3&"'!B4:B1000"), A2, INDIRECT("'"&$G$2:$G$3&"'!BU4:BU1000")).
Finally, wrap the entire SUMIF formula in SUMPRODUCT to add the arrays together: =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$3&"'!B4:B1000"),A2,INDIRECT("'"&$G$2:$G$3&"'!BU4:BU1000"))).
Sum Across Multiple Sheets Easily in WPS Office
WPS Spreadsheet fully supports complex array formulas like SUMPRODUCT, SUMIFS, and INDIRECT, offering a seamless experience for organizing and calculating data across multiple worksheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your multiple data sheets and summary sheet.
- 2. Navigate to the summary sheet: Go to the master summary sheet where you want to display the consolidated totals.
- 3. Enter the nested formula: Type the =SUMPRODUCT(SUMIF(INDIRECT(...))) formula into the target cell, referencing your specific list of sheet names and data ranges.
- 4. Calculate the result: Press Enter. WPS Spreadsheet will instantly calculate and return the summed values matching your criteria across all specified sheets.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error across sheets?
This usually happens if the sheet names in your reference list do not exactly match the actual worksheet names, or if the sheet names contain spaces and you forgot to wrap the INDIRECT reference strings in single apostrophes (e.g., "'"&$G$2&"'!A1").
Can I use SUMIFS instead of SUMIF with INDIRECT across multiple sheets?
Yes, you can replace SUMIF with SUMIFS if you need to evaluate multiple criteria. The structure remains the same, but you will wrap the SUMIFS function inside SUMPRODUCT and use INDIRECT for each criteria range and the sum range.
Do I need to use an array formula (Ctrl+Shift+Enter) for this combination?
No, because the SUMPRODUCT function is specifically designed to handle arrays natively. You can simply press Enter after typing the formula.
How do I handle new sheets being added dynamically?
You should define your sheet name list as a dynamic named range or format it as a Table. As you add new sheet names to the list, the INDIRECT formula will automatically include them in the calculation without needing manual updates.
