To split a bill unevenly, give each person a weight for each item, divide each item by the total weight, and add up a person's shares. In Google Sheets or Excel that is one SUMPRODUCT per person. The one extra step is rounding, so that the shares add up to the exact bill.
Set up items and weights
Say five friends have a dinner. The main course and drinks come to ₹4,260 and desserts to ₹600, so the bill is ₹4,860. Two people skipped dessert. Lay it out like this, with items in rows and people in columns:
| Item | Amount | Asha | Ravi | Meera | Kabir | Neha | Total weight |
|---|---|---|---|---|---|---|---|
| Main and drinks | 4260 | 1 | 1 | 1 | 1 | 1 | 5 |
| Dessert | 600 | 1 | 1 | 1 | 0 | 0 | 3 |
Items sit in rows 2 and 3, amounts in B2:B3 and weights in C2:G3. The total weight in H2 is =SUM(C2:G2), copied down to H3. A weight of 0 means the person skipped the item. A weight of 2 would mean a double share, such as a person who also brought a guest.
What is the SUMPRODUCT share formula?
A person's share is each item amount times their weight, divided by the item's total weight, added up. For Asha, in C5:
=SUMPRODUCT($B$2:$B$3, C2:C3/$H$2:$H$3)
Copy it right to G5. The division inside SUMPRODUCT works as an array in both Google Sheets and Excel, so no special entry is needed. The check cell is =SUM(C5:G5), which should equal the bill.
| Person | Main and drinks | Dessert | Share |
|---|---|---|---|
| Asha, Ravi, Meera | ₹852 | ₹200 | ₹1,052 each |
| Kabir, Neha | ₹852 | ₹0 | ₹852 each |
The arithmetic: 3 x 1,052 = 3,156 and 2 x 852 = 1,704, which add up to ₹4,860.
How do you stop rounding from breaking the total?
Not every bill divides cleanly. A ₹1,000 bill among three people is ₹333.33 each, and three of those make ₹999.99, one paisa short. Round each share to paise and let one person carry the leftover:
- Shares for all but the last person: =ROUND(SUMPRODUCT($B$2:$B$3, C2:C3/$H$2:$H$3),2)
- Last person's share: =SUM($B$2:$B$3)-SUM(C5:F5)
- Check cell: =SUM(C5:G5)-SUM($B$2:$B$3), which should show 0
Choose the carrier by agreement, usually whoever paid the bill, so the odd paisa stays with them.
What about tax, service charge and tip?
Add them as separate item rows instead of mixing them into the amounts. If a service charge applies to everything, give it weights equal to each person's total food and drink, which keeps it proportional. If it is a flat amount for the table, give everyone weight 1. Either way the formula in row 5 does not change, because it already reads every row from 2 to 3. When you add item rows, insert them above the share row and extend the ranges in the formula to cover them.
What if everyone orders their own items?
Then flip the weights. List every item with its price in column B, and put a 1 in a person's column for each item they ate or drank, leaving the rest blank. A shared starter gets a 1 for everyone who tucked in, so its price is divided among them. The same SUMPRODUCT formula still works, because a blank cell counts as zero. For a quick equal split with no weights, =ROUND(B1/COUNTA(A2:A6),2) divides one bill among the names listed in A2:A6. Use the weighted layout when the table is uneven, and the one-line version when it is not.
Keeping your own share of it
The rest of the table is a one-off. What belongs in your own expenses is only your share. SpendixAI is not a bill-splitting app, so enter your own share as the expense, using a hand entry, rather than the whole restaurant charge from your statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
