Add a Paid by column to your expense log, total each person with SUMIFS, and compute the settle-up as half the difference. If Rohan paid ₹18,500 and Meera paid ₹11,500 of ₹30,000 shared costs, each owes ₹15,000, so Meera owes Rohan ₹3,500. The formulas below do that automatically for any two people.
How should the sheet be laid out?
Use one row per expense. Column A is the date, B the item, C the amount, D Paid by with a dropdown of the two names, and E Shared, with a dropdown of Shared or Own. The E column matters because not everything bought in a household is split. A personal haircut paid by one person is not part of the settle-up.
How do I total what each person paid?
- Type the names in G2 and G3, spelled exactly as in the dropdown.
- In H2 enter =SUMIFS($C$2:$C$500,$D$2:$D$500,G2,$E$2:$E$500,"Shared") and fill down to H3.
- In H4 enter =SUM(H2:H3) for the shared total.
- In H5 enter =H4/2 for each person's fair share.
Rows marked Own are left out of both totals, because the SUMIFS asks for Shared.
How does the settle-up cell work?
Each person's balance is what they paid minus their share. In I2 enter =H2-$H$5 and fill down to I3. A positive number means the other person owes them. With two people the amounts are equal and opposite, so one cell is enough. In H7 enter =(H2-H3)/2, where H2 is the first person's total. If it is positive, the second person owes the first. A readable message:
- =IF(H7>0,G3&" owes "&G2&" ₹"&TEXT(H7,"#,##0"),IF(H7<0,G2&" owes "&G3&" ₹"&TEXT(-H7,"#,##0"),"All settled"))
Worked example with made-up numbers
| Item | Paid by | Amount (₹) |
|---|---|---|
| Monthly rent | Rohan | 14,000 |
| Grocery order | Meera | 6,500 |
| Electricity and Wi-Fi | Rohan | 4,500 |
| Cook's salary and gas cylinder | Meera | 5,000 |
Rohan paid ₹14,000 + ₹4,500 = ₹18,500. Meera paid ₹6,500 + ₹5,000 = ₹11,500. The shared total is ₹30,000 and each share is ₹15,000. The settle-up is (18,500 − 11,500) / 2 = ₹3,500, owed by Meera to Rohan. Check: Rohan paid ₹3,500 more than his share, and Meera paid ₹3,500 less than hers.
What if the split is not 50/50 or there are three of you?
For three flatmates sharing equally, the share is the shared total divided by 3, and each person's balance is what they paid minus that share. Use =H4/COUNTA(G2:G4) so the divisor follows the number of names. For an unequal split, such as 60/40 by income, put each person's percentage in a column beside their name and calculate share as the total multiplied by that percentage. The rest is unchanged.
What goes wrong?
- A name spelt two ways, such as Rohan and Rohan K, creates a third person and breaks the totals.
- Forgetting to mark a row Shared or Own leaves it out of everything.
- Amounts typed as text return zero in SUMIFS, so test with ISNUMBER.
- Cash paid by one person for the other is easy to forget. Make logging it a habit.
A small habit helps more than any formula: enter the row when you pay, from your phone, while the bill is still in your hand. Households that wait until the end of the month forget cash payments and small UPI transfers, and those are exactly the ones that cause arguments. If a payment is for the household but was made from a personal account, it still goes in with your name in Paid by.
When a sheet stops being enough
SpendixAI is a personal expense tracker. It is not a bill-splitting app and has no settle-up feature, so a sheet like this one is the right tool for the shared part. What it does well is your own side, importing your statements and categorising your spending in your own workspace.
Month-end routine
- Check the Paid by column has no blanks.
- Read the settle-up message and pay it by UPI.
- Log the settlement in the separate table, then start a fresh month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

