JOIN SHEET
Get Started

How to filter categories by their total, not individual amounts

Find accounts whose combined transactions reach your target amount. Add up each account first so smaller transactions are included before you filter the totals.

JOIN SHEETPublished Updated
Five transactions across three accounts include small individual amounts that add up above the threshold.
Illustration with fictional data, not a screenshot of the app.

To find accounts that spent at least 100,000 in total, add up their transactions first and filter the totals afterward. That keeps accounts that reached the target through several smaller purchases. In Refine results, place Summarize above the Filter that checks the total.

Two smaller transactions can exceed the threshold together

In this fictional list, Company A has amounts of 60,000 and 70,000. Neither transaction reaches 100,000, but together they total 130,000. Company B totals 90,000, while Company C has one transaction of 110,000.

Account Amount
Company A 60,000
Company A 70,000
Company B 40,000
Company B 50,000
Company C 110,000

Filtering Amount for values of at least 100,000 first would keep only Company C. That would incorrectly exclude Company A from a report about account totals.

Put the total filter after the summary

  1. Load and select the sheet, open Refine results, and add Summarize with Account under Group by.
  2. Choose Sum of Amount and name the output Account total.
  3. Below the summary, add Filter. Select Account total and Greater than or equal to, then enter 100000 without a comma.
  4. For the order shown here, add Sort on Account total with Descending, then choose Apply.

Diagram of summarizing by account, filtering totals of at least 100000 and sorting largest first.

Make sure the filter uses Account total, the field created by the summary. It contains each account's combined spending; the original Amount column contains individual transactions.

Expected result: Company A and Company C

Account Account total
Company A 130,000
Company C 110,000

The result has two rows totaling 240,000. The original total was 330,000; the difference is Company B's excluded 90,000, not a lost transaction.

Diagram of the two accounts that pass the total threshold.

When should a filter come before the summary?

Put the filter first when it chooses which transactions to include. For “completed transactions totaling at least 100,000,” use status filter → account summary → total filter. This adds up only completed transactions, then keeps the accounts whose completed total reaches the target.

Check that Amount is numeric and review any existing sheet filters. These steps apply to the full result, including the Excel file you save, even when only part of it appears in the preview.

Try it with your own files

Start with JOIN SHEET ↗

Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.

Example last verified:

Sources

Excel pivot table does not filter — Stack Overflow

The fictional account totals illustrate why filtering before or after a summary gives different results.

Related articles