How to Total Category Expenses Across Monthly Excel Worksheets
Question details
The user needs to calculate and summarize specific category totals (such as administration, fundraising, and meetings) across multiple monthly worksheets.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating monthly expense tracking into a master summary sheet by extracting and summing values based on specific expense categories.
- Observed behavior
- Requires a dynamic formula setup to pull and sum values from identically structured monthly sheets based on matching category criteria.
Ensure that all your monthly worksheets share the exact same structure, meaning the expense categories and amounts are consistently located in the same columns across all sheets.
Use SUM, SUMIFS, and INDIRECT Functions
This combination allows you to dynamically sum specific categories across a defined list of worksheet names without manually clicking through each sheet.
The INDIRECT function dynamically references your list of sheet names, SUMIFS finds the specific category within each sheet, and SUM adds all the matching results together into a single total.
Create a list of all your monthly worksheet names (e.g., Jan, Feb, Mar) in a dedicated column on your master summary sheet.
Select this list of worksheet names, click into the Name Box (located to the left of the formula bar), type 'Sheets', and press Enter to create a named range.
Assuming your target category is in A2, the monthly categories are in C2:C1000, and amounts in E2:E1000, select the cell where you want the total and enter: =SUM(SUMIFS(INDIRECT("'"&Sheets&"'!E2:E1000"),INDIRECT("'"&Sheets&"'!C2:C1000"),A2))
Press Enter to calculate the total, then drag the fill handle down to apply this formula to the remaining categories in your summary.
Easily Consolidate Your Monthly Expenses with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including SUMIFS and INDIRECT, allowing you to manage complex financial tracking across multiple worksheets effortlessly. It is highly compatible with Microsoft Excel formats and provides a smooth experience for consolidating data.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your consolidated expense workbook containing the monthly tabs.
- 2. Define the Sheets Range: List your monthly sheet tabs on the summary page and use the Name Box to name the range 'Sheets'.
- 3. Enter the Formula: Input the =SUM(SUMIFS(INDIRECT(...))) formula in your master sheet to tally the specific category expenses.
- 4. Copy Down: Use the drag-fill handle to copy the formula for all other expense categories instantly.
Frequently Asked Questions
Why is my formula returning a #REF! error?
A #REF! error usually occurs if one of the worksheet names in your 'Sheets' named range is misspelled, contains extra spaces, or the sheet does not actually exist in the workbook.
Can I use 3D references instead of INDIRECT for conditional summing?
Standard 3D references (like =SUM(Jan:Dec!E2)) work great for summing fixed cells across sheets, but they do not support conditional summing formulas like SUMIFS. For category-based conditional sums across sheets, the SUMIFS with INDIRECT method is required.
Do my monthly sheets need to be formatted identically?
Yes, the formula references specific fixed columns (e.g., C2:C1000 for categories and E2:E1000 for amounts). If a monthly sheet has data in different columns, the formula will not calculate correctly.
How do I handle spaces in my worksheet names?
The formula automatically accounts for spaces by wrapping the sheet names in single quotes within the INDIRECT function. As long as the exact text in your 'Sheets' list matches the tab names, spaces will not break the calculation.
