Consolidate Matching Customers and Sum Financial Years in Excel Sheets
Question details
The user needs to combine matching customer records from four worksheets and calculate total amounts for every customer and financial year on a fifth worksheet.
- Product
- Microsoft Excel workbook with multiple customer-by-year worksheets
- Device & OS
- not provided
- Scenario
- Several worksheets contain similarly structured customer tables for different business lines, and a summary worksheet is needed for customer and financial-year totals.
- Observed behavior
- INDEX/MATCH formulas return #N/A when a customer does not appear on one or more source worksheets, preventing reliable totals.
Make sure every source table uses the same column structure, such as Business Line, Customer, Financial Year, and Amount. Standardize customer names and financial-year labels first so that the same customer is not treated as separate entries.
Combine the source tables and summarize them with a PivotTable
Use this method when you need a refreshable summary of all customers and financial years without writing lookup formulas for each worksheet.
Appending the four source tables into one normalized list avoids missing-customer lookup errors. A PivotTable groups identical customer and financial-year values and sums the Amount column automatically.
On each of the four worksheets, add a column named Business Line and enter the source area name, such as Prevention, EAP, Recovery, or Digital, for every row. Confirm that each table also has Customer, Financial Year, and Amount columns.
Create a new worksheet named Consolidated. Copy the data rows from each source table below the same headers so all records form one continuous list; include the Business Line value with each record.
Select any cell in the consolidated data, then choose Insert > PivotTable. Select the full data range or table and place the PivotTable on the summary worksheet.
In the PivotTable Fields pane, drag Customer to Rows, Financial Year to Columns, and Amount to Values. If Amount is not totaled, open Value Field Settings and choose Sum.
After appending new or revised records to the Consolidated sheet, click inside the PivotTable and choose Refresh to recalculate the customer totals.
Use SUMIFS against a consolidated customer data table
Use this method when the fifth worksheet needs a formula-based report layout rather than a PivotTable.
Create Customer and Financial-Year Totals with WPS Spreadsheet
WPS Spreadsheet lets you consolidate customer records into one table, create a PivotTable for a refreshable year-by-year report, or use SUMIFS formulas for a custom summary layout. Its familiar spreadsheet interface helps you move existing Excel data into a cleaner reporting workflow.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the four customer data worksheets.
- 2. Create a consolidated data worksheet: Click the plus button beside the sheet tabs to add a worksheet, name it Consolidated, and paste the source records under common Business Line, Customer, Financial Year, and Amount headers.
- 3. Insert a PivotTable: Select the consolidated data, then choose Insert > PivotTable and place the report on a new or existing summary worksheet.
- 4. Configure the summary fields: In the PivotTable field list, place Customer in Rows, Financial Year in Columns, and Amount in Values, then ensure the value calculation is Sum.
Frequently Asked Questions
Why does INDEX/MATCH return #N/A for some customers across worksheets?
INDEX/MATCH returns #N/A when the customer name is absent from the lookup range on a particular worksheet. Use IFNA around the lookup to show 0, or consolidate the records and use SUMIFS or a PivotTable instead.
Can SUMIFS total the same customer from several business lines?
Yes. After all business-line records are appended to one consolidated range, SUMIFS can match the Customer and Financial Year columns and add every matching Amount value.
What should I do when customer names are spelled differently on different sheets?
Correct the names before consolidation so each customer has one consistent name. Extra spaces, abbreviations, and alternate spellings create separate PivotTable items and prevent SUMIFS matches.
How can I show zeros instead of blanks in a customer PivotTable?
Right-click the PivotTable, choose PivotTable Options, open the Layout & Format section, and enter 0 in the option for empty cells. The exact label may vary by spreadsheet version.
Do I need to include the Business Line column in the final report?
No. Keep Business Line in the consolidated data for traceability, but you can omit it from the PivotTable layout or SUMIFS criteria when the report only requires totals by Customer and Financial Year.
