Travel and vehicles

Organise a multi-city work trip claim by day and city

Organise a multi-city business trip claim by day and city, with personal and claimable columns, per-city subtotals and a three-city, five-day sample.

Editorial illustration for Organise a multi-city work trip claim by day and city

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.

DateCityItemAmountClaimable
5 DecBengaluruAirport cab₹850Y
5 DecBengaluruHotel, night 1₹5,400Y
5 DecBengaluruDinner with a friend₹1,200N
6 DecBengaluruClient lunch₹640Y
6 DecBengaluruHotel, night 2₹5,400Y
7 DecHyderabadFlight from Bengaluru₹4,300Y
7 DecHyderabadHotel, night 3₹4,800Y
7 DecHyderabadSouvenirs₹900N
8 DecChennaiFlight from Hyderabad₹3,900Y
8 DecChennaiHotel, night 4₹4,600Y
9 DecChennaiAirport cab₹700Y

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.

CityClaimablePersonal
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?

  1. A summary at the top: trip purpose, dates, cities and total claimed.
  2. One folder per city, with receipts named by date and amount.
  3. Travel tickets in date order.
  4. 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports