How to Sum Data Across Multiple Excel Sheets Based on Row Conditions
Question details
The user needs to create a summary across 31 worksheets by checking if a specific column meets a condition (e.g., contains 'Liv') and summing the matching values for a specific person.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing daily or categorized data across multiple worksheets (like 31 days in a month) based on specific row criteria.
- Observed behavior
- Needs a formula to search through all 31 sheets, validate the row condition, and aggregate the corresponding data without errors caused by merged cells.
Ensure that all your worksheets have an identical structure and layout. Unmerge any merged cells in your data range, as they can cause formula errors and complicate cross-sheet array calculations.
Use the SUMPRODUCT and INDIRECT Functions to Summarize Data
The SUMPRODUCT function combined with INDIRECT can evaluate multiple criteria across a dynamic list of different sheets and sum the corresponding values efficiently.
Using SUMPRODUCT combined with the INDIRECT function allows you to dynamically reference a list of sheet names (e.g., Day 1 to Day 31) and apply your criteria.
It is highly recommended to remove merged cells from your data source before applying this formula, as merged cells disrupt array calculations and cause #VALUE! errors.
In your summary sheet, create a list of all the sheet names you want to calculate (e.g., in cells A2:A32). Name this range 'SheetList' using the Name Box next to the formula bar.
Go through the worksheets and unmerge any cells in the columns you are evaluating. Select the merged cells, go to the 'Home' tab, and click 'Merge & Center' to unmerge them.
In your summary sheet, enter the formula: =SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!Sum_Range"), INDIRECT("'"&SheetList&"'!Criteria_Range1"), "*Liv*", INDIRECT("'"&SheetList&"'!Criteria_Range2"), "PersonName")). Replace 'Sum_Range', 'Criteria_Range1', and 'Criteria_Range2' with your actual column references (e.g., E:E).
Press Enter to calculate the formula. It will now search all 31 sheets, check if the row condition in column E is met, and sum the values for the specified person.

Easily Manage and Analyze Data Across Sheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT and INDIRECT, making it easy to summarize data across multiple worksheets. Its intuitive interface also helps you quickly unmerge cells and organize your data for error-free calculations.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your multi-sheet workbook containing the data you want to summarize.
- 2. Clean your data format: Navigate to the 'Home' tab and use the 'Merge and Center' dropdown to unmerge any cells that might interfere with array formulas.
- 3. Input the array formula: Select the target cell in your summary sheet, enter your SUMPRODUCT and INDIRECT formula referencing your sheet list, and press Enter to instantly aggregate your data across all sheets.

Frequently Asked Questions
Why do merged cells cause problems when searching across multiple sheets?
Merged cells alter the underlying grid structure of the spreadsheet. When functions like SUMPRODUCT or SUMIFS try to evaluate arrays, merged cells cause array size mismatches and reference errors, leading to incorrect calculations or #VALUE! errors.
Can I use 3D references with SUMIFS instead of SUMPRODUCT?
The SUMIFS function does not natively support 3D references (e.g., Sheet1:Sheet31!A1:A10). To work around this, you must combine SUMIFS with SUMPRODUCT and INDIRECT to process an array of sheet names.
What if the sheet names contain spaces?
If your sheet names contain spaces, you must enclose the sheet name reference in single quotes within your INDIRECT formula. For example, use INDIRECT("'" & SheetList & "'!A1:A10") to ensure the formula resolves the sheet names correctly.
How do I search for a partial text match like 'Liv' in the formula?
To search for partial text, you can use wildcard characters in the SUMIFS criteria. Placing asterisks around the text, such as "*Liv*", tells the formula to count the cell if 'Liv' appears anywhere within the text string.




