Money tips

Your Top Merchants This Month: Where the Money Really Goes (UK Edition)

Work out which merchants take most of your monthly spending using SUMIF and a clean-name column, with worked pound examples and a simple ranking.

Editorial illustration for Your Top Merchants This Month: Where the Money Really Goes (UK Edition)

The quickest way to find your top merchants is to give every transaction a clean merchant name, then use SUMIF to add up each name and rank the totals. Do it once for a month of current-account and card spending and you will see which five or six names account for most of it.

Start with a clean-name column

Statement text rarely reads as the name you use for a shop. The same supermarket can appear with a branch name, a town and a reference number, and a streaming service may show the company behind it. In your sheet, keep the raw text in column B and type the name you would use in conversation in column C. For the first month that is manual; after that, copy the same names down whenever the raw text repeats.

How do you total each merchant with SUMIF?

Put the list of clean names in column F, starting at F2. Amounts of spending sit in column D and clean names in column C, rows 2 to 400. Then fill these in row 2 and copy them down:

  • G2: =SUMIF($C$2:$C$400, F2, $D$2:$D$400) gives the total spent with that merchant.
  • H2: =COUNTIF($C$2:$C$400, F2) gives the number of visits.
  • I2: =G2/H2 gives the average per visit.
  • J2: =G2/SUM($D$2:$D$400) gives the share of the month; format column J as a percentage.

To pull out the unique names without typing them, Excel 365 and recent Google Sheets accept =UNIQUE($C$2:$C$400). In older Excel versions, type the list by hand or use a pivot table.

A worked month in pounds

Say a month's card and account spending, leaving out rent and bills paid by direct debit, comes to £1,850. These figures are made up to show the sums.

MerchantVisitsTotalAverageShare of £1,850
Supermarket11£396£36.0021.4%
Train and tube travel20£190£9.5010.3%
Takeaway app7£168£24.009.1%
Pub and bar tabs5£145£29.007.8%
Streaming and music3£31£10.331.7%

Check one line: £396 divided by 11 visits is £36.00, and £396 divided by £1,850 is 21.4%. The five lines add to £396 + £190 + £168 + £145 + £31 = £930, which is just over half of the month at 50.3%.

Which number should worry you: total or visits?

Neither on its own. High total with few visits, like the supermarket, usually means a necessity. High visits with a small average, like travel, usually means routine. The one to question is the middle: the takeaway app with seven visits at £24. Seven orders is a pattern you can change, whereas one £396 shop is not.

What about shared or split purchases?

If you pay a restaurant bill for four and friends pay you back, your ranking will show the full amount against the restaurant and the transfers as credits. Either net the repayments against the bill so the restaurant shows only your share, or keep a note column and subtract it before totalling. Without this, a friendly Saturday dinner can look like the biggest line of the month. Do the same for a flat share where one person pays the household bills and the others reimburse them.

What to do with the ranking

  1. Circle the two merchants whose visit count surprised you.
  2. Ask whether each of them is a choice, a routine or a convenience.
  3. Set a rough limit for one of them next month, based on this month's figure, not a target pulled from nowhere.
  4. Come back in four weeks and compare the same two lines.

If a monthly sheet starts to feel like too much work, an expense tracker can do the merchant totals for you, but a sheet like this is the right place to learn what you are looking for.

SPENDIXAI JOURNAL

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

Explore your reports