A group expense sheet needs a date, an item, an amount, who paid, who shares it, and a settled flag. The trick is a column for each person with a 1 where they share the cost. Then three formulas give every person's share, what they paid and what they are owed or owe. Here is the layout and the arithmetic.
What columns do I need?
| Column | Heading | Purpose |
|---|---|---|
| A | Date | When it happened |
| B | Item | What was bought |
| C | Amount | Total paid |
| D | Paid by | Name of the payer |
| E to H | One column per person | Put 1 if the person shares this item, leave blank if not |
| I | Number sharing | =SUM(E2:H2) |
| J | Share per head | =IF(I2=0,0,C2/I2) |
| K | Settled? | Yes or No |
The IF in column J stops a division by zero on blank rows. Names go in the headings of E1 to H1, say Ravi, Meena, Joy and Kiran. These formulas work in Excel and Google Sheets.
How do I calculate the balances?
In a small block elsewhere, for each person, work out three numbers.
- Paid: =SUMIFS($C$2:$C$200,$D$2:$D$200,"Ravi")
- Owes: =SUMPRODUCT(E$2:E$200,$J$2:$J$200), which multiplies Ravi's 1s by each row's share and adds them
- Balance: Paid minus Owes. Positive means the group owes that person; negative means that person owes the group.
For the next person, change the name in the first formula and use the matching column letter in the second.
Can I see a worked example?
Four friends, three expenses, invented figures. The cab of ₹1,200 was paid by Ravi and shared by all four, so ₹300 each. Dinner of ₹1,800 was paid by Meena and shared by Ravi, Meena and Joy, so ₹600 each. Tickets of ₹1,600 were paid by Joy and shared by Joy and Kiran, so ₹800 each.
| Person | Paid | Owes | Balance |
|---|---|---|---|
| Ravi | ₹1,200 | ₹900 | +₹300 |
| Meena | ₹1,800 | ₹900 | +₹900 |
| Joy | ₹1,600 | ₹1,700 | -₹100 |
| Kiran | ₹0 | ₹1,100 | -₹1,100 |
Check the totals: paid is ₹4,600 and owed is ₹4,600, and the balances add to zero. Ravi owes 300 + 600 = 900, and so does Meena; Joy owes 300 + 600 + 800 = 1,700; Kiran owes 300 + 800 = 1,100.
How do we settle with the fewest payments?
Match those who owe with those who are owed. Kiran owes ₹1,100: pay ₹900 to Meena and ₹200 to Ravi. Joy owes ₹100: pay it to Ravi. Ravi has then received ₹300, and Meena ₹900. Three payments close everything. Mark column K as Yes for every row once the money has moved.
Which mistakes break the sheet?
- Names typed two ways, such as Ravi in one row and Ravi K in another. The SUMIFS will miss one of them.
- Text in the Amount column, such as ₹1,200 typed with the symbol and a space. Type numbers only and format the column as currency.
- A share flag left blank for someone who actually shared the cost.
- A person added halfway through the trip without a new column. Add the column and fix the earlier rows' flags.
If the check cell is not zero, scan the rows from the top and compare the number sharing against what really happened.
What if a share is not equal?
Add a second sheet or a note with each person's exact amount, and replace the 1s with those amounts. Then change column J to a plain sum instead of dividing. Keep to equal shares where you can; unequal ones are harder to follow.
When is a sheet not enough?
The sheet is for the group. SpendixAI is for your own money, and it is not a splitting app: it can take your bank statement and show your own spending by category, while the group sheet tells you how much of a payment was really yours.
Before the trip
- Create the sheet and share it with everyone.
- Agree that the payer enters each expense on the same day.
- Photograph bills in case a figure is questioned later.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


