Travel and vehicles

How to see your spending by country on a multi-country trip

Split trip spending by country using dates, a country column and simple formulas, with a worked three-country example and a way to handle border-day costs.

Editorial illustration for How to see your spending by country on a multi-country trip

To see spending by country, add a Country column to your trip sheet and fill it from your itinerary dates, then total each country with SUMIFS. Statements do not name the country reliably, so the itinerary is your key: any payment dated inside a country's stay belongs to that country, apart from a few border days you settle by hand.

Why do statements not answer this question?

A card statement gives a date, a merchant and an amount, often in rupees. It might show a city, but usually not a country, and some merchants bill from a different country than where you ate. Online bookings made at home for a stay abroad belong to the stay's country, not the date you paid.

How do you set up the itinerary table?

  1. Write one line per country: country, arrival date, departure date.
  2. Make sure dates in the transaction list and the itinerary are real dates, not text.
  3. Add the Country column to the transaction list.
  4. Fill it with a lookup, or by hand for a short trip.

Keep the itinerary on a separate sheet named Trip, with country in column A and arrival date in column B, sorted by arrival date. Then in the transaction sheet, with the payment date in B2, this fills the country: =IFERROR(LOOKUP(B2, Trip!$B$2:$B$4, Trip!$A$2:$A$4), "Booked before trip"). It works in Excel and Google Sheets because it takes the country of the latest arrival on or before the payment date. For a short trip you can also type the country by hand.

How do you total each country directly?

With the amount in column D of the transactions sheet and dates in column B, the spend for one country row in the itinerary is: =SUMIFS(Transactions!$D$2:$D$500, Transactions!$B$2:$B$500, ">="&B2, Transactions!$B$2:$B$500, "<="&C2). Put it beside each itinerary line and fill down.

A worked example with made-up numbers

CountryDatesSpend in rupees
Spain3 to 8 May₹42,300
France9 to 13 May₹38,900
Italy14 to 20 May₹51,600
Booked before the tripFlights, one hotel deposit₹46,200

The four lines add up to ₹1,79,000. By country, Italy is the largest at ₹51,600, about 29 percent of the total, and the 7-day stay costs ₹7,371 a day (51,600 divided by 7), against France at ₹7,780 a day (38,900 divided by 5). A per-day figure is more useful than the total, because the stays were different lengths.

How do you handle border days and booked-ahead costs?

  • Border days: decide a rule, such as the country you slept in, and use it for every crossing.
  • Train or flight between countries: keep it in its own travel line instead of splitting it.
  • Hotel booked in rupees before the trip: give it the country of the stay.
  • Shared dinners paid by one person: record your own share only, if you know it.

How do you handle shared costs and refunds?

Record only your own share of shared bills, with the full amount noted beside it. Put refunds in the same country as the original charge, even if the refund arrives after you have left, otherwise that country looks cheaper than it was. If you paid a deposit that was returned, show the deposit and the return as a pair so they cancel out, and leave a note so you remember why two lines exist.

What can an expense tracker add?

In SpendixAI you can filter transactions by date and merchant and run reports that group your spending, then download the result as CSV. So a date filter for each leg of the trip gives you that leg's total. The country itself is not something the app works out, so you apply the dates yourself.

SPENDIXAI JOURNAL

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

Explore your reports