Function Problems

How to Use SUMPRODUCT Across Multiple Excel Sheets with Two Criteria

Adam Davis Oct 10, 2026 869 views

Question details

Calculate total data across multiple worksheets requiring exact matches for both a date heading and a unique identifier on a consolidated master sheet.

How to Use SUMPRODUCT Across Multiple Excel Sheets with Two Criteria

Product: Excel
Device & OS: not provided
Scenario: Consolidating numerical data from multiple worksheets (e.g., 11 sheets) into a single master sheet where dates and unique identifiers vary and do not perfectly align across all sheets.
Observed behavior: A standard single-criterion formula sums the incorrect rows because it only matches the date and ignores the shifting unique identifiers. A dual-criterion approach is required.

Before you start

Ensure that all your individual worksheets share a consistent structure for column headers and data types, as standardizing your layout is critical before performing multi-sheet consolidation.

Solution 1Recommended

Consolidate Data Using Power Query and SUMIFS

Instead of using a complex and error-prone formula across multiple sheets, combining the data into one master table using Power Query is the most reliable method for multi-criteria calculations.

Because each sheet contains different date ranges and subsets of identifiers, evaluating arrays dynamically across sheets with INDIRECT and SUMPRODUCT causes high processing loads and frequent errors.

Using Power Query to flatten the 11 worksheets into a single, consolidated dataset allows you to use a much simpler SUMIFS function or a PivotTable.

Import worksheets with Power Query

Open your Excel workbook, navigate to the Data tab on the ribbon, and select Get Data > From File > From Workbook to begin importing your multiple worksheets.

Append queries into one table

In the Power Query Editor, use the 'Append Queries' feature to stack all 11 worksheets into a single master table. Ensure that the Date and Identifier columns align perfectly, then click 'Close & Load' to place this consolidated data into a new worksheet.

Calculate totals with SUMIFS

On your final master sheet, use the SUMIFS function referencing your new consolidated table. An example formula is: =SUMIFS(ConsolidatedTable[Value], ConsolidatedTable[Date], MasterDateCell, ConsolidatedTable[Identifier], MasterIDCell).

Alternative: Use a PivotTable

Alternatively, select your consolidated Power Query table, go to Insert > PivotTable, and place your Identifiers in the Rows area, Dates in the Columns area, and Values in the Values area to automatically calculate totals.

Consolidate Data Using Power Query and SUMIFS

Performance Boost: Power Query prevents formula lag in large workbooks. Since the data is flattened first, calculating multiple criteria becomes exceptionally fast.

Solution 2

Use SUMPRODUCT and INDIRECT for Multi-Sheet Calculations

If you cannot use Power Query and must rely purely on formulas, you can combine SUMPRODUCT, SUMIFS, and INDIRECT to evaluate arrays across a predefined list of sheet names.

Advanced Data Consolidation

Easily Handle Multi-Sheet Calculations with WPS Spreadsheet

WPS Spreadsheet provides robust tools for consolidating data across multiple worksheets. With full support for advanced array formulas and built-in Data Consolidation tools, managing complex criteria becomes a breeze.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple data worksheets.
2. Consolidate your data: Navigate to the Data tab and select 'Consolidate' to merge data structurally, or insert a PivotTable for dynamic multi-criteria reporting.
3. Apply advanced formulas: Type your =SUMIFS() or =SUMPRODUCT() formulas directly into the master sheet cells exactly as you would in standard spreadsheet software.

Full compatibility with Microsoft Excel functions including SUMPRODUCT, SUMIFS, and INDIRECT.Built-in Data Consolidation and intuitive PivotTable features to quickly summarize multi-sheet data without complex formulas.Lightweight architecture ensuring fast calculation speeds even when handling numerous heavy worksheets.Free to use with a familiar, easy-to-navigate user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return a #REF! error when referencing multiple sheets?

SUMPRODUCT cannot natively evaluate 3D references (like Sheet1:Sheet11!A1:A10) directly. You must use the INDIRECT function combined with a named range of your sheet names to evaluate arrays across different worksheets successfully.

Can I use the SUMIFS function across multiple sheets without combining them first?

SUMIFS is inherently designed to work on a single sheet. To sum across multiple sheets using SUMIFS, you must wrap it inside a SUMPRODUCT function and use INDIRECT to point to a list of sheet names, which tricks the formula into calculating one sheet at a time and adding the results.

Why is Power Query recommended over SUMPRODUCT for this task?

Power Query appends data into a single, clean table, which allows you to use much simpler, non-volatile formulas like SUMIFS. Formulas utilizing INDIRECT and SUMPRODUCT across dozens of sheets are highly volatile, meaning they recalculate with every sheet change, which severely slows down workbook performance.

Excel & Data Problems