Business

Group expenses by merchant and month with UNIQUE and SUMIFS (Europe)

Create a merchant-by-month expense report that builds its own merchant list with UNIQUE. Euro example for a shared flat, for Excel 365 and Google Sheets.

Editorial illustration for Group expenses by merchant and month with UNIQUE and SUMIFS (Europe)

If you use Excel 365, Excel 2021 or Google Sheets, the UNIQUE function can build your list of merchants automatically, so a new supplier appears in the report without you typing anything. Pair it with a helper column for the month and SUMIFS for the amounts. Older Excel versions do not have UNIQUE; use the pivot-table route there.

What are we building?

Four people share a flat in Rotterdam and pay everything on one card: groceries, internet, laundry, cleaning supplies. They want to see, for each shop, how much went out each month, without maintaining a list by hand. The merchant list should grow by itself.

Step 1: add a month column

With dates in A, merchant in B and amount in C, add a helper in D: =TEXT(A2,"yyyy-mm"). This turns 14 May 2026 into the text 2026-05. Note that in spreadsheets set to some European languages the format letters differ, for example "jjjj-mm" in a Dutch or German setup, so adjust to your locale if you see an error or the letters themselves.

Step 2: let the sheet list the merchants

In G2, type =SORT(UNIQUE(FILTER($B$2:$B$500, $B$2:$B$500<>""))). The FILTER skips empty rows, UNIQUE removes duplicates and SORT puts them in alphabetical order. The list spills downwards automatically.

Step 3: fill the grid

In H1, I1 and J1 type the months as text: 2026-03, 2026-04, 2026-05. Format those cells as Text first so the sheet does not turn them into dates. Then in H2:

=SUMIFS($C$2:$C$500, $B$2:$B$500, $G2, $D$2:$D$500, H$1)

Fill right and down. If the merchant list grows, extend the formulas down to match; the list itself will grow without help, but the grid formulas do not spill by themselves unless you use a dynamic approach.

Shop2026-032026-042026-05
Grocery chain€286.40€301.15€274.90
Internet provider€39.00€39.00€39.00
Laundrette€18.50€22.00€15.50

March: 286.40 + 39.00 + 18.50 = €343.90. April: 301.15 + 39.00 + 22.00 = €362.15. May: 274.90 + 39.00 + 15.50 = €329.40. Over three months that is 343.90 + 362.15 + 329.40 = €1,035.45. The shop names and amounts are invented. If four flatmates divide it evenly, each share of the March total is 343.90 / 4 = €85.975, so €85.98 once rounded.

Why do two shops appear for the same business?

UNIQUE is exact: "Grocery Chain" and "Grocery chain " count as different strings, the second with a trailing space. Clean the source first. A helper column =TRIM(B2) takes care of spaces. For labels that differ more, such as a branch code, build a small two-column alias table and look it up with =IFERROR(VLOOKUP(TRIM(B2), $L$2:$M$30, 2, FALSE), TRIM(B2)).

What if we would rather not maintain formulas?

An expense app can do the grouping. SpendixAI has a report builder where you filter and group, save the report and export it as CSV. It is built around India and INR, so it is not something I can recommend for a euro household; I mention it to show what the feature set looks like.

Showing the share each merchant takes

A table of amounts hides proportions. Add a column to the right of the grid that divides each merchant's total by the grand total: =K2/SUM($K$2:$K$20) formatted as a percentage. In the flat example the grocery chain's three-month total is 286.40 + 301.15 + 274.90 = €862.45, and 862.45 / 1,035.45 is roughly 83 percent of the card's spending on these three shops. Seeing one line dominate makes the next conversation about the household budget concrete, and it takes one extra column.

Finishing checklist

  • Does the sum of the grid equal the sum of the raw amount column?
  • Are the month labels text and typed in the same format the helper column produces?
  • Have you kept an untouched copy of the exported file?
SPENDIXAI JOURNAL

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

Explore your reports