Power Query Problems

How to Sum Date-Range Data from Multiple Excel Sheets

Huma Ashraf Ch Oct 8, 2026 870 views

Question details

The user needs to consolidate and sum referral-fee records from approximately 45 Excel worksheets into a single master sheet, summarized by month and vendor within a specific date range.

How to Sum Date-Range Data from Multiple Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Consolidating monthly business data across dozens of worksheets to avoid manual checking and calculation.
Observed behavior: Data is scattered across 45 separate worksheets, making it extremely time-consuming to calculate monthly vendor totals for a specific date range without an automated summary.

Before you start

Before combining the data, ensure that all worksheets have identical column headers (such as Date, Vendor, Project, and Referral Fee) and consistent data types so that they can be seamlessly merged.

Solution 1Recommended

Use Power Query to Combine Sheets and Summarize with a PivotTable

This is the most efficient method for merging dozens of worksheets and aggregating the data by date range and vendor without manual copying.

Power Query allows you to treat all worksheets in a workbook as a single data source. Once the data is appended, you can easily filter dates and use a PivotTable to summarize totals.

Load data into Power Query

Go to the Data tab on the Ribbon, click 'Get Data' > 'From File' > 'From Workbook', and select your current Excel file. Alternatively, create an Excel Table in each sheet and use 'Get Data' > 'From Other Sources' > 'Blank Query' with the formula =Excel.CurrentWorkbook().

Expand the table content

In the Power Query Editor, locate the 'Content' column and click the expand icon (two diverging arrows) at the top of the column header to reveal all data from the 45 sheets.

Filter the required date range

Click the dropdown arrow on your 'Date' column, select 'Date/Time Filters' > 'Between...', and enter your required start and end dates to filter out irrelevant records.

Load to a PivotTable

Click 'Close & Load' dropdown at the top left, select 'Close & Load To...', choose 'PivotTable Report', and place it on your master sheet.

Configure the summary view

In the PivotTable Fields pane, drag 'Date' (group it by Month) and 'Vendor' to the Rows area, and drag 'Referral Fee' to the Values area to see your final summarized totals.

Use Power Query to Combine Sheets and Summarize with a PivotTable

Automated Updates: When new data is added to the worksheets, simply right-click anywhere in the master PivotTable and select 'Refresh' to instantly update the totals.

Solution 2

Use SUMIFS and INDIRECT Functions for Summary

If you prefer not to use Power Query, you can set up a master sheet with a list of sheet names and use formulas to dynamically extract and sum totals based on criteria.

Combine Data Easily in WPS Office

Use WPS Spreadsheet to Consolidate Monthly Vendor Data

WPS Spreadsheet offers powerful data consolidation, seamless Excel compatibility, and robust PivotTable features to help you quickly merge dozens of worksheets and summarize referral fees by month and vendor.

1. Open the workbook in WPS: Launch WPS Spreadsheet and open your existing file containing the 45 referral-fee worksheets.
2. Use Data Consolidation: Navigate to the Data tab and click 'Consolidate' to select and merge the data ranges from your multiple sheets into a master list.
3. Create a PivotTable: Go to the Insert tab, select 'PivotTable', and apply it to your newly consolidated data range.
4. Summarize by Month and Vendor: Drag the Date field (grouped by month) and Vendor field to the Rows area, and place the Referral Fee in the Values area to finish your summary.

High compatibility with Microsoft Excel (.xlsx) formats and advanced formulasIntuitive Data Consolidation and PivotTable tools for quick and accurate summariesLightweight and runs smoothly even with workbooks containing 45+ data-heavy sheetsFree to download and completely easy to use

microsoft office alternative - wps office

Frequently Asked Questions

Can I automate this process if new worksheets are added later?

Yes. If you use Power Query (via the From Folder or Excel.CurrentWorkbook method), any new worksheet added to the workbook will automatically be included in your summary the next time you click 'Refresh' on the Data tab.

Why is my PivotTable grouping dates by individual days instead of months?

By default, the software may not auto-group dates. To fix this, right-click any date inside your PivotTable, select 'Group', and choose 'Months' to summarize your referral fees properly by month.

Does the column order need to be identical on every sheet for Power Query to work?

No. As long as the column headers are spelled exactly the same across all sheets, Power Query will automatically align the data into the correct columns, even if their order varies from sheet to sheet.

How do I handle worksheets that have missing data or blank cells?

When combining sheets in Power Query, empty cells will appear as 'null'. You can easily filter out these 'null' values directly in the Power Query editor before loading the data into your final PivotTable.

Excel & Data Problems