How to Sum Budget Amounts by Drop-Down Code Across Multiple Sheets in WPS Office Excel
Question details
The user needs to total budget amounts by a drop-down code listed in multiple project worksheets and create one budget summary.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A workbook contains separate project worksheets with drop-down codes and amount columns. The user wants a summary sheet that totals each code across all project worksheets.
- Observed behavior
- The workbook contains separate project sheets with drop-down codes and amounts; the user is looking for a maintainable summary instead of manually adding values from each sheet.
Before you start, confirm that each project sheet uses the same Code and Amount column layout and that the drop-down code values are consistent, because SUMIFS and PivotTables group only matching text values.
Consolidate Project Data and Sum by Code with SUMIFS or a PivotTable
Use this method when you want an easy-to-audit budget summary and can add one consolidated sheet to the workbook.
WPS Office Excel can quickly total repeated codes after the project rows are brought into a single table. Keeping one consolidated range makes formulas and PivotTables easier to update as projects change.
In WPS Office Excel, click the plus icon next to the sheet tabs to insert a new sheet named Consolidated. In row 1, enter Code and Amount, then copy the Code and Amount cells from each project sheet and paste them as rows below the same headers.
Insert another sheet named Budget Summary. In cell A1 type Code and in B1 type Total. Enter each drop-down code you want to total in column A, or copy the Code column from Consolidated and use Data > Remove Duplicates to list unique codes.
In cell B2 of Budget Summary, enter the formula =SUMIFS(Consolidated!B:B,Consolidated!A:A,A2) and press Enter. Drag the fill handle down to apply the formula to all codes.
Select any cell in the Consolidated data, click Insert > PivotTable, and place Code in the Rows area and Amount in the Values area. This automatically creates an itemized budget summary and can be refreshed after data changes.
Use INDIRECT and SUMIF to Total Codes Directly Across Project Sheets
Use this formula-only method when you cannot or prefer not to create a consolidated table and every project sheet has the same layout.
Create a Budget Summary by Drop-Down Code in WPS Office Excel
Use WPS Office Excel to consolidate project sheets and total each drop-down code with SUMIFS or a PivotTable. You get a single summary that is easy to update while keeping full compatibility with Excel files.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the project worksheets. Click the plus icon next to the sheet tabs to add a new sheet named Consolidated.
- 2. Consolidate project rows: Enter Code and Amount as headers in the Consolidated sheet. Go to each project sheet, select the Code and Amount cells, copy them, and paste them below the matching headers in Consolidated.
- 3. Build the summary: Add a Budget Summary sheet. List the unique drop-down codes in column A, then click in cell B2 and type =SUMIFS(Consolidated!B:B,Consolidated!A:A,A2).
- 4. Or insert a PivotTable: Click any cell in Consolidated, choose Insert > PivotTable, and drag Code to Rows and Amount to Values to create the totals automatically.
- 5. Update future data: After adding rows for new projects, extend the SUMIFS range or refresh the PivotTable so the budget summary includes the latest amounts.
Frequently Asked Questions
Can I total amounts by drop-down code without copying all sheets into one table?
Yes. You can use a formula with INDIRECT and a named sheet list, but it is more sensitive to sheet names and layout. A consolidated table with SUMIFS or a PivotTable is easier to update and audit in WPS Office Excel.
How do I create a unique list of drop-down codes for the budget summary?
Copy the Code column from all project sheets into one column in a new sheet, select the column, and click Data > Remove Duplicates in WPS Office Excel. The remaining values are your unique codes.
What should I do when I add another project worksheet?
If you are using a consolidated table, paste the new Code and Amount rows into the Consolidated sheet and either drag the SUMIFS range to include the new rows or refresh the PivotTable.
Does WPS Office Excel support Microsoft Excel files and SUMIFS formulas?
Yes. WPS Office Excel opens and saves .xlsx, .xls, and .csv files, and it supports SUMIFS, PivotTable, and data tools such as Remove Duplicates, so your budget summary remains compatible.
Why does my SUMIFS return 0 even though codes exist?
Usually the drop-down code text differs by spaces, capitalization, or hidden characters. Use the same source list for all drop-downs or add a helper column with TRIM/CLEAN to normalize values.
