Function Problems

How to Sum Data Across Multiple Excel Sheets Based on Criteria

Kushani Nimanthika Sep 30, 2026 870 views

Question details

The user needs to sum matching values from multiple Excel worksheets based on a specific condition, such as a school type stored in cell A1.

How to Sum Data Across Multiple Excel Sheets Based on a Specific Criterion

Product: Excel
Device & OS: not provided
Scenario: Consolidating and summarizing categorized numerical data (e.g., C3:F6) spread across different worksheets into a master summary.
Observed behavior: Instead of writing complex multi-sheet conditional formulas, the user requires an efficient method to aggregate values based on criteria across separate tabs.

Before you start

Ensure that all source worksheets have a consistent data structure and identical column headers before attempting to consolidate the data.

Solution 1Recommended

Combine Data into a Single Excel Table and Use SUMIFS

Merging your scattered worksheets into one master table is the most robust and recommended way to handle criteria-based calculations in Excel.

Keeping raw data on separate sheets makes conditional calculations like SUMIFS very difficult because Excel does not natively support 3D referencing for the SUMIFS function. Combining the data into one structured table resolves this issue completely.

Create a Master Worksheet

Add a new worksheet to your workbook. Copy the data ranges from all individual school worksheets and paste them vertically into this single sheet.

Add a Criteria Column

Insert a new column next to your data named 'School Type'. For each pasted block of data, enter the corresponding school type. You can also derive this from the original sheet name instead of relying on a single reference cell like A1.

Convert to an Excel Table

Select your entire combined data range. Go to the 'Insert' tab on the ribbon and click 'Table' (or press Ctrl+T). Check 'My table has headers' and click OK.

Apply the SUMIFS Function

In your summary area, use the SUMIFS function to add up values based on the new column. For example: =SUMIFS(Table1[Amount], Table1[School Type], A1) where A1 contains the school type you want to sum.

Combine Data into a Single Excel Table and Use SUMIFS

Best Practice: Storing raw data in a single table with category columns aligns with database best practices, making future analysis much easier.

Solution 2

Summarize Consolidated Data Using a PivotTable

A PivotTable is a powerful, formula-free alternative for summarizing large datasets categorized by specific types.

Simplify Data Summaries

Summarize Multi-Sheet Data Effortlessly in WPS Spreadsheet

WPS Office offers intuitive tools like the SUMIFS function and advanced PivotTables, making it incredibly simple to consolidate, organize, and analyze data across multiple worksheets in seconds.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your multi-sheet document.
2. Consolidate your data: Copy your separate sheet data into a single master sheet and add a 'School Type' criteria column.
3. Insert a PivotTable: Go to the Insert tab, select PivotTable, and drag your fields to instantly summarize totals by category.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Includes a seamless, highly responsive PivotTable interface for quick data summarization.Lightweight software that handles large, multi-sheet datasets without lagging.

microsoft office alternative - wps office

Frequently Asked Questions

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

Excel does not natively support 3D references within the SUMIFS function. To conditionally sum across multiple sheets without combining them, you would need to write complex array formulas combining SUMPRODUCT, SUMIFS, and INDIRECT with a named range of all your sheet names. Combining the data into one table is much simpler and less prone to errors.

How can I automatically extract the worksheet name to use as my criteria?

You can extract the current sheet name into a cell using this formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). You can then reference this cell as your category identifier instead of typing it manually.

Why is it better to use a single master table instead of separate worksheets?

Keeping data on separate identically formatted sheets violates basic data organization principles. A single master table with a category column (like 'School Type') allows you to easily sort, filter, apply PivotTables, and use basic formulas without complicated cross-sheet referencing.

Excel & Data Problems