For a trip that covers several cities, keep one sheet with one row per expense and columns for date, city, item, amount and whether it is claimable. Then total each city with a SUMIFS formula. Reviewers can follow the trip day by day, and your personal spends stay visibly separate from the work ones.
Which columns should the sheet have?
- A: Date.
- B: City.
- C: Item, such as hotel, cab, lunch with client.
- D: Amount in rupees.
- E: Claimable, with Y for work costs and N for personal ones.
If a flight or train moves you between cities, put it under the city you arrive in and say so at the top of the sheet. Whatever rule you choose, use it throughout.
What does a three-city, five-day sample look like?
These are invented numbers for a trip from 5 to 9 December: Bengaluru, then Hyderabad, then Chennai.
| Date | City | Item | Amount | Claimable |
|---|---|---|---|---|
| 5 Dec | Bengaluru | Airport cab | ₹850 | Y |
| 5 Dec | Bengaluru | Hotel, night 1 | ₹5,400 | Y |
| 5 Dec | Bengaluru | Dinner with a friend | ₹1,200 | N |
| 6 Dec | Bengaluru | Client lunch | ₹640 | Y |
| 6 Dec | Bengaluru | Hotel, night 2 | ₹5,400 | Y |
| 7 Dec | Hyderabad | Flight from Bengaluru | ₹4,300 | Y |
| 7 Dec | Hyderabad | Hotel, night 3 | ₹4,800 | Y |
| 7 Dec | Hyderabad | Souvenirs | ₹900 | N |
| 8 Dec | Chennai | Flight from Hyderabad | ₹3,900 | Y |
| 8 Dec | Chennai | Hotel, night 4 | ₹4,600 | Y |
| 9 Dec | Chennai | Airport cab | ₹700 | Y |
How do I total each city?
With the data in rows 2 to 60, use SUMIFS. The order is: the range to add, then each range and its condition.
- Bengaluru claimable: =SUMIFS($D$2:$D$60,$B$2:$B$60,"Bengaluru",$E$2:$E$60,"Y")
- Hyderabad claimable: =SUMIFS($D$2:$D$60,$B$2:$B$60,"Hyderabad",$E$2:$E$60,"Y")
- Chennai claimable: =SUMIFS($D$2:$D$60,$B$2:$B$60,"Chennai",$E$2:$E$60,"Y")
- All personal spends: =SUMIFS($D$2:$D$60,$E$2:$E$60,"N")
- Everything: =SUM($D$2:$D$60)
SUMIFS works the same way in Excel and Google Sheets. Keep the city names identical in every row; a stray space or a different spelling will drop a line out of the subtotal.
| City | Claimable | Personal |
|---|---|---|
| Bengaluru | ₹12,290 | ₹1,200 |
| Hyderabad | ₹9,100 | ₹900 |
| Chennai | ₹9,200 | ₹0 |
| Total | ₹30,590 | ₹2,100 |
Check the numbers: Bengaluru is 850 + 5,400 + 640 + 5,400, which is ₹12,290. Hyderabad is 4,300 + 4,800, which is ₹9,100. Chennai is 3,900 + 4,600 + 700, which is ₹9,200. Together they make ₹30,590. Add the personal ₹2,100 and the whole sheet comes to ₹32,690, which should match =SUM($D$2:$D$60).
What should I do about a personal item inside a work bill?
Say you have dinner with a friend on a day you are on a business trip. Put it in the sheet as a personal row, with N, even if it is on the same bill as a work meal. Where a single bill mixes both, split it into two rows and write the logic in a note. Do not blur personal lines into claimable ones.
What do I attach with it?
- A summary at the top: trip purpose, dates, cities and total claimed.
- One folder per city, with receipts named by date and amount.
- Travel tickets in date order.
- A note on anything missing.
If you paid by card or UPI, SpendixAI shows each payment as a transaction once you upload the statement, with the receipt kept alongside. The claimable and personal split is a claim matter, so keep it in your sheet.
Final check
If you move between cities late at night, the date can be ambiguous: a flight landing at 12:30 am belongs to the next day's city but the previous day's spend. Pick the date printed on the ticket or the bill and use it everywhere.
- Every row has a date, city, amount and Y or N.
- City names are spelt the same throughout.
- Subtotals add up to the grand total.
- Receipts exist for each claimable row, or a note says why not.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

