How to Sum Values Across Multiple Excel Sheets by Name or Category
Question details
The user needs to calculate the total amount for specific names or categories spread across several identically structured worksheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating categorical or financial data that has been separated into multiple sheets (e.g., one sheet per month or store) into a single summary view based on a specific criteria.
- Observed behavior
- The user needs a reliable method to conditionally sum the data across all sheets without manually calculating each row.
Ensure that all the worksheets you want to sum have the exact same column layout and that the category names in your criteria match exactly without any hidden leading or trailing spaces.
Use SUMPRODUCT and INDIRECT for Many Sheets
Best for workbooks with many sheets, allowing you to sum by category dynamically by referencing a list of sheet names.
This approach is highly scalable. Instead of typing a SUMIF formula for every single sheet, you create a list of your sheet names in one place. The INDIRECT function dynamically reads these sheet names, SUMIF calculates the totals per sheet based on your criteria, and SUMPRODUCT adds them all together into a single total.
Create a list of all your worksheet names in a separate column on your summary sheet (for example, in cells H1 to H10).
Select the cell containing the specific category name or criteria you want to sum (for example, cell A2).
In your result cell, enter the formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$10&"'!C:C"),A2,INDIRECT("'"&$H$1:$H$10&"'!E:E")))
Replace C:C with the column containing your criteria and E:E with the column containing the values to sum. Press Enter to calculate the combined total.
Manually Add SUMIF Formulas for a Few Sheets
The simplest method when you only have a small number of sheets to consolidate.
Combine Data Using VSTACK and GROUPBY (Microsoft 365)
A modern, clean approach for Microsoft 365 users to dynamically stack arrays and group them by category.
Sum Across Multiple Sheets Efficiently in WPS Spreadsheet
WPS Spreadsheet supports advanced array formulas like SUMPRODUCT, SUMIF, and INDIRECT, allowing you to easily consolidate and summarize data across multiple worksheets. It offers a lightweight, user-friendly interface that feels instantly familiar.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet workbook.
- 2. Create a sheet list: On your summary sheet, create a list of your target worksheet names in a single column.
- 3. Enter the dynamic formula: Type the formula =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$10&"'!C:C"),A2,INDIRECT("'"&$H$1:$H$10&"'!E:E"))) into the result cell.
- 4. Calculate results: Press Enter to instantly calculate the consolidated total across all specified sheets.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
This usually happens if the sheet names in your helper list do not exactly match the actual sheet tab names, or if a listed sheet has been deleted. Check for typos or extra spaces in your list and ensure the sheet names are enclosed in single quotes within the formula if they contain spaces.
Can I use 3D references with the SUMIF function?
No, the SUMIF function does not natively support 3D references (like Sheet1:Sheet5!A1:A10). To sum conditionally across multiple sheets, you must either add multiple SUMIF formulas together, use the SUMPRODUCT/INDIRECT workaround, or use VSTACK in newer Excel versions.
What is the best way to structure data to avoid complex multi-sheet formulas?
The best practice is to keep all transactional data in a single master worksheet. Instead of creating a new sheet for every month or location, add a new column for 'Month' or 'Location' to distinguish the records. You can then use a simple PivotTable or SUMIFS to easily summarize the data.
Does the VSTACK and GROUPBY method work in older versions of Excel?
No, VSTACK and GROUPBY are modern dynamic array functions available only in Microsoft 365 and the latest versions of Excel. For older versions, you should use the Power Query append method or the SUMPRODUCT and INDIRECT formula.
