Spreadsheets

Self-building category summary with UNIQUE and SUMIFS

Build a dynamic summary table in Excel 365 where UNIQUE lists your categories and SUMIFS totals them, so a new category appears with no formula edits.

Editorial illustration for Self-building category summary with UNIQUE and SUMIFS

Use UNIQUE to list the categories and SUMIFS to total them, with the spill reference pointing SUMIFS at the whole list. Put =UNIQUE(FILTER(B2:B500,B2:B500<>"")) in E2 and =SUMIFS($C$2:$C$500,$B$2:$B$500,E2#) in F2. Both formulas spill by themselves, so a category typed into the log tomorrow shows up in the summary with no formula touched. This needs Excel 365 or Excel 2021; Excel 2019 and earlier do not have UNIQUE.

What does the setup look like?

Say your log has Date in column A, Category in B and Amount in C, with rows running down to about row 500. Leave E1 and F1 for headings (Category and Total) and keep the cells below them empty, because a spill needs free space.

  • E2: =SORT(UNIQUE(FILTER($B$2:$B$500,$B$2:$B$500<>"")))
  • F2: =SUMIFS($C$2:$C$500,$B$2:$B$500,E2#)
  • H1 (a check cell for the grand total): =SUM(F2#)

FILTER removes the blank rows, which matters because UNIQUE would otherwise return a 0 or an empty entry for the unused part of the range. SORT puts the labels in alphabetical order. The # after E2 means the whole spilled list, wherever it currently ends.

Why does this update by itself?

UNIQUE recalculates every time the log changes. If you type a new category into column B, the list in E grows by one row, the spill reference E2# now covers one more label, and SUMIFS returns one more total. Nothing is copied down or dragged. The old approach of typing category names into a summary and forgetting one is what this removes.

A worked example with made-up numbers

Say the log holds five entries:

CategoryAmount (₹)
Food450
Fuel800
Food620
Rent18,000
Fuel1,100

The summary shows Food ₹1,070 (450 plus 620), Fuel ₹1,900 (800 plus 1,100) and Rent ₹18,000, and the total cell shows ₹20,970. Now add a row for Pets with ₹1,250. The summary gains a fourth line, Pets ₹1,250, and the total becomes ₹22,220, without any edit to E2 or F2.

How do I make the summary respect a month?

Type the first day of the month in H2, for example 1-Nov-2025, and add two date conditions to SUMIFS. The month filter belongs in the totals and in the label list, otherwise a category with no spending this month still appears as zero:

  • E2: =SORT(UNIQUE(FILTER($B$2:$B$500,($B$2:$B$500<>"")*($A$2:$A$500>=$H$2)*($A$2:$A$500<EDATE($H$2,1)))))
  • F2: =SUMIFS($C$2:$C$500,$B$2:$B$500,E2#,$A$2:$A$500,">="&$H$2,$A$2:$A$500,"<"&EDATE($H$2,1))

EDATE($H$2,1) gives the first day of the next month, so "less than" that date catches every day of the chosen month including the last. If nothing matches, FILTER returns #CALC!, which you can tame by adding a third argument such as "None" inside FILTER.

Why do I get #SPILL! or two lines for the same category?

Can I sort by the biggest spend and use Google Sheets?

To rank categories by amount, put =SORTBY(E2#,F2#,-1) in J2 for the labels and =SORTBY(F2#,F2#,-1) in K2 for the totals, largest first. Both are Excel 365 and 2021 functions.

Google Sheets has UNIQUE and FILTER too, but no spill reference symbol. Use =SORT(UNIQUE(FILTER(B2:B500,B2:B500<>""))) in E2 and =ARRAYFORMULA(IF(E2:E="","",SUMIF($B$2:$B$500,E2:E,$C$2:$C$500))) in F2.

Before you trust the summary

  1. Compare =SUM($C$2:$C$500) with =SUM(F2#). A difference means an amount belongs to a blank category or a text amount sits in column C.
  2. Add a new test category, check it appears, then delete the test row.
  3. Keep the log as an Excel Table if you like; then use the table column names in place of the fixed ranges and the range grows on its own.

If maintaining the log is the slow part, SpendixAI sorts statement rows into categories using your own rules first and your history next, and a Report builder lets you filter and group the result.

SPENDIXAI JOURNAL

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

Explore your reports