Calculation Issues

How to Sum Values Across Multiple Excel Worksheets by Month

Huda Qurayshi Sep 25, 2026 870 views

Question details

The user needs to calculate the total values for individuals across several monthly worksheets (e.g., JUL24 through MAR25) on a Summary sheet, specifically filtering by a selected start and end month.

How to Sum Values Across Multiple Excel Worksheets by Month

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a dynamic summary report that aggregates financial or performance data from individual monthly tabs based on a user-defined date range.
Observed behavior: Data is currently split across multiple monthly tabs, requiring a consolidated structure to perform date-range specific sums for each person.

Before you start

Ensure that all your monthly worksheets have an identical structure, with the same column headers in the exact same order, to guarantee accurate consolidation.

Solution 1Recommended

Use VSTACK and SUMIFS Functions to Calculate Totals

In modern versions of Excel, the VSTACK function allows you to combine data from multiple worksheets into a single virtual array, which can then be analyzed using SUMIFS.

This method is ideal if you are using Microsoft 365 or a version that supports dynamic arrays. It allows you to create a dynamic formula without manually altering the workbook's data structure.

Prepare Date Criteria

On your Summary sheet, designate two cells for your Start Month and End Month criteria (for example, cell B1 for the start date and cell B2 for the end date).

Combine Sheets with VSTACK

Use the formula =VSTACK('JUL24:MAR25'!A2:D100) to vertically stack the data ranges from all the monthly sheets into one contiguous array in memory.

Apply SUMIFS to the Stacked Data

Wrap the VSTACK array within a SUMIFS or FILTER formula. Configure the criteria ranges to evaluate the date column in your stacked array against the start and end dates in cells B1 and B2.

Use VSTACK and SUMIFS Functions to Calculate Totals

3D Reference Tip: When referencing multiple sheets in your formula, you can click the first sheet tab (e.g., JUL24), hold the Shift key, and click the last sheet tab (e.g., MAR25) to automatically generate the 'JUL24:MAR25' 3D reference.

Solution 2

Append Monthly Sheets Using Power Query

Power Query provides a robust, scalable way to combine multiple worksheets into one structured table, making it easy to filter and summarize with a PivotTable.

Efficient Data Consolidation

Sum Data Across Multiple Sheets Easily with WPS Office

WPS Spreadsheet provides powerful built-in tools like the Data Consolidate feature and dynamic array functions to seamlessly merge and sum data from multiple monthly worksheets without complex formula nesting.

1. Open the Consolidate Tool: Open your workbook in WPS Spreadsheet, navigate to the Data tab on the top ribbon, and click on the 'Consolidate' button.
2. Select the Sum Function: In the Consolidate dialog box that appears, choose 'Sum' from the Function drop-down menu to aggregate your values.
3. Add Monthly Data Ranges: Click the Reference box, select the relevant data range on your first monthly sheet, and click 'Add'. Repeat this process for each month, check 'Create links to source data', and click OK to generate your dynamic summary.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatsBuilt-in 'Consolidate Data' tool simplifies cross-sheet summarization nativelyFamiliar user interface requires no additional learning curveLightweight and fast, ensuring smooth performance even with dozens of monthly worksheets

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the SUM function across multiple sheets without filtering by a specific month?

Yes, you can use a 3D reference for simple cell-by-cell addition. Type `=SUM('JUL24:MAR25'!B2)` to add the value of cell B2 from all worksheets situated between the JUL24 and MAR25 tabs.

What if my monthly sheets have different column orders?

If your columns are not in the exact same order across all sheets, using Power Query to append the sheets is the best approach. Power Query automatically aligns and combines data based on matching column header names rather than their physical position.

Why is my VSTACK formula returning a #NAME? error?

The VSTACK function is a dynamic array function only available in newer versions of Excel (such as Microsoft 365 or Office 2021). If you are using an older version, the function will not be recognized, and you should use the Power Query method instead.

Excel & Data Problems