Function Problems

Calculate Expenses Paid by Two People Across Multiple Excel Sheets

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to calculate the total expenses paid by two specific individuals (identified by 'P' and 'L') across multiple worksheets and combine these totals onto a master summary sheet.

Product: Excel
Device & OS: not provided
Scenario: Tracking and consolidating shared expenses between two people across multiple identical daily or monthly worksheets.
Observed behavior: Requires conditionally summing values based on a payer identifier within individual sheets, and then aggregating those specific cells across multiple worksheets onto a single summary sheet.

Before you start

Ensure that all the worksheets you plan to summarize share the exact same layout and structure, as 3D referencing requires identical cell positioning across sheets.

Solution 1Recommended

Use SUMIFS and 3D SUM Formulas to Consolidate Expenses

This method calculates individual totals on each sheet using SUMIFS and then aggregates them on a summary sheet using a 3D SUM formula, requiring no VBA scripting.

If all your worksheets are structured identically, you do not need to use complex VBA macros. The built-in SUMIFS function can conditionally extract the expenses for each person on individual sheets, and a 3D reference SUM function can pull those totals into your summary sheet.

Calculate individual totals per sheet using SUMIFS

In each individual worksheet, select an empty cell (e.g., G2) and enter `=SUMIFS($B:$B,$D:$D,"P")` to calculate the total for person P. Repeat this in cell G3 with `=SUMIFS($B:$B,$D:$D,"L")` for person L, adjusting column references so $B:$B is your expense amount and $D:$D is the payer identifier.

Calculate combined totals for each sheet

Use a simple SUM formula like `=SUM(G2:G3)` to calculate the combined total expenses for both people on that specific worksheet.

Aggregate totals on the summary sheet

Navigate to your summary sheet. To combine the totals for person P from all sheets, enter a 3D SUM formula such as `=SUM(Sheet1:Sheet5!$G$2)`. For person L, use `=SUM(Sheet1:Sheet5!$G$3)`. Adapt the sheet names (Sheet1:Sheet5) to match the actual names of the first and last sheets in your workbook.

Formatting Tip: If your worksheet names contain spaces, Excel requires single quotes around the sheet names in the formula, such as =SUM('Jan Data:Mar Data'!$G$2).

Manage Expenses in WPS Spreadsheet

Easily Calculate Shared Expenses with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS and 3D referencing, allowing you to seamlessly manage and track shared expenses across multiple sheets without any lag or missing features.

1. Open your workbook in WPS Spreadsheet: Launch WPS Spreadsheet and open the file containing your multiple expense worksheets.
2. Apply conditional sums: Use the =SUMIFS function to extract the amounts paid by each specific individual on each daily or monthly tab.
3. Create your master summary: Add a new sheet at the beginning of your workbook to act as the Summary tab.
4. Consolidate the data: Use the 3D SUM formula (e.g., =SUM(Sheet1:Sheet5!G2)) to instantly pull and combine the totals from all the other tabs.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and standard formulas.Seamlessly handles advanced arrays, SUMIFS, and 3D referencing for complex expense tracking.Lightweight, free to use, and offers a familiar user interface with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate totals across multiple sheets without using VBA?

Yes. As long as your worksheets have exactly the same structure, you can use a combination of SUMIFS for conditional calculations within the sheet, and a 3D SUM formula (like =SUM(Sheet1:Sheet5!A1)) on your summary sheet to consolidate the data without any VBA.

What is the correct syntax for the SUMIFS function?

The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, ...). The sum_range is the column containing the numbers to add, the criteria_range is the column containing the identifiers (like names or 'P'/'L'), and criteria is the specific identifier you want to match.

Why am I getting an error with my 3D SUM formula?

This typically happens if your sheet names contain spaces but lack single quotation marks in the formula. Make sure your formula looks like =SUM('Sheet 1:Sheet 5'!$G$2). Errors also occur if you attempt to use 3D referencing with functions that don't support it, but standard SUM is fully supported.

Excel & Data Problems