Splitting group expenses in Google Sheets is easy. You can use my template or my array formula that uses the SPLIT_EXPENSE named function.
If each expense is shared commonly, you may not need a spreadsheet.
For example, if four people went on a group vacation and all expenses were shared equally among all members, the calculation would be simple. However, if all expenses were shared equally among only beneficiaries, you can use my template or formula.
The good news is that my solution works in both of these scenarios.
To use the split group expenses template, simply enter the expense details in the “Expense” tab, including who paid for what, who is a beneficiary, and the amount paid. The array formula in the “Split” tab will automatically calculate who owes whom and how much.
You can enter the details as soon as the expense occurs since it is easy to do. It will only take a few minutes, and the summary will always be available at your fingertips.
Input:
Output:
Usage Notes
1. Enter the team member names (beneficiaries) in cell range D3:3.
2. Enter the expense details in cells A4:A (expense), B4:B (who paid), and C4:C (paid amount).
3. Specify the beneficiaries by choosing “Yes” or “No” in cells under D3:3 (under the beneficiaries).
The split group expenses will be calculated in the “Split” tab.
Important: All beneficiary names in cells D3:D3 must be present in column B (even if no payment was made). For example, if Person 5 does not make a payment and appears in D3:3, enter their name in column B. You can leave columns A and C blank in the corresponding row. Please check cell range A1:C11 within my split expense template below.
How to Use Array Formula to Split Group Expenses in Google Sheets
One of the surprising elements of the split group expense template is the following array formula used in cell D2 in the Split tab.
=SPLIT_EXPENSE(
Expenses!B4:B,
Expenses!C4:C,
FILTER(Expenses!D4:R,Expenses!D3:3<>""),
FILTER(Expenses!D3:3,Expenses!D3:3<>"")
)It populates the entire table output by itself.
SPLIT_EXPENSE is a custom function specifically for splitting group expenses in Google Sheets.
Syntax:
SPLIT_EXPENSE(paid_by, amt_paid, split, split_h)Arguments:
paid_by: A vertical one-dimensional array that contains the names of the members of the group. It can contain duplicate names.amt_paid: The amount againstpaid_by. It should also be a vertical one-dimensional array and equal in size topaid_by.split: A 2-D array that contains drop-downs with a “Yes” or “No” option. The number of columns in this array should match the number of unique names inpaid-by. The number of rows in this array should match the number of rows inpaid_by.split_h: A header row containing the unique names inpaid_by. It’s a one-dimensional horizontal array. The size of it should match the number of columns in thesplit.
How to Use SPLIT_EXPENSE Named Function in Google Sheets
In fact, you can find the use of the SPLIT_EXPENSE function in cell D2 (Split tab) of my above template. However, I also used the FILTER function with the split and split_h arguments to filter out blank columns from an infinite column range.
Let’s try the SPLIT_EXPENSE function to split group expenses of a closed range. This way, you can easily understand how to use it and implement it in your own sheet without any problems.
Example:
=SPLIT_EXPENSE(B4:B8,C4:C8,D4:F8,D3:F3)paid_by: B4:B8amt_paid: C4:C8split: D4:D8split_h: D3:F3
When you go through the split expenses (result), you can understand one thing: B and C should pay a total of 5250 to A. In that, B’s contribution is 1250 and C’s contribution is 4000.
The usage note for the split expense template above applies here as well. This means that all of the names in D3:F3 must be present in B4:B8.
Conclusion
How do I get the SPLIT_EXPENSE named function to split group expenses in Google Sheets and does it cost anything?
You can get it for free by making a copy of my template. To use it in any other file, you need to import it. You can find the instructions for importing it in my NAMED FUNCTIONS guide.
