Spreadsheets

QUERY order by and limit: top merchants by spend in Google Sheets

Find your top 10 merchants by spend in Google Sheets with QUERY: group by merchant, sum amount, order by sum descending, limit 10, and label the header.

Editorial illustration for QUERY order by and limit: top merchants by spend in Google Sheets

To list your top 10 merchants in Google Sheets, use QUERY to group by merchant, sum the amount, order by that sum in descending order and add limit 10. Put the label clause after limit to give the sum column a readable header.

What is the formula?

Assume dates in A, merchant in B, amount in C, headers in row 1 and data down to row 500:

  • =QUERY(A1:C500, "select B, sum(C) where B is not null group by B order by sum(C) desc limit 10 label B 'Merchant', sum(C) 'Total spend'", 1)

Step by step: select the merchant and the sum; ignore rows with no merchant; group so each merchant appears once; sort by the sum with the largest first; keep only the first ten rows; then rename the columns. The order of the clauses matters, and limit comes after order by, which is why the ten kept are the ten biggest.

What does the output look like?

MerchantTotal spend
Supermarket A9,800
Cab service6,400
Online grocer5,950
Electricity board4,200

These are made-up figures to show the shape. Rows run from the largest total down, and the sheet will show ten if your data has ten merchants.

How do I stop the same merchant appearing twice?

Grouping is case-sensitive, so SWIGGY and Swiggy become separate rows. Group on a cleaned version instead:

  • =QUERY(A1:C500, "select upper(B), sum(C) where B is not null group by upper(B) order by sum(C) desc limit 10 label upper(B) 'Merchant', sum(C) 'Total spend'", 1)

That fixes capital letters but not spelling differences such as Swiggy and Swiggy Instamart, or long narrations that differ only by a reference number. A tidy merchant column, produced by a cleaning formula, gives better totals.

How do I rank only a financial year or only debits?

  • Add a date filter before group by: where A >= date '2025-04-01' and A <= date '2026-03-31'.
  • Combine with an and, for example: where B is not null and C > 0.
  • If your sheet stores spending as negative numbers, flip the test and sum the amounts, then multiply by minus one in a neighbouring column.

What about the next ten?

Add offset 10 after limit 10 to skip the first ten and show the next ten. The pair limit 10 offset 10 gives merchants ranked 11 to 20.

How is this different from SORTN?

SORTN returns the top rows of a range after sorting, but it does not add up amounts per merchant for you, so you would need a summarised list first. QUERY groups, sums, sorts and trims in a single formula.

Mistakes to avoid

  1. Selecting the amount without sum, which fails once you group.
  2. Writing order by C when the select shows sum(C). Order by the same expression.
  3. Putting limit before order by.
  4. Leaving the header argument off. Add the final 1.

How much of my spending do the top ten explain?

Put the total spend in a cell, such as =SUM(C2:C500), and compare. Say the whole range adds up to 60,000 rupees and the three biggest merchants total 22,150. Then those three account for 22,150 divided by 60,000, which is about 0.37, or 37% of the spend. That tells you whether the list is worth studying or whether the money is scattered across many small merchants.

A worked sanity check: if the top three totals are 9,800, 6,400 and 5,950, together they come to 22,150. Compare that against a hand total for those three merchants from your statements. If your statements are long, SpendixAI can import them and its reports can show merchant spend without the formula upkeep.

SPENDIXAI JOURNAL

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

Explore your reports