How to sum by category with one formula in Excel
Excel formula
=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))Pair UNIQUE with SUMIFS: =HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13))) returns each category with its total.
- Result on the example
- a 4-row list that spills from E2 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE lists the categories in the order they first appear. Given that list as its criteria, SUMIFS returns one total per category, and HSTACK places the totals beside the names. New categories in the data appear in the summary on their own, which a list of typed-in SUMIFS formulas can't do.
The Google Sheets version uses SUMIF inside ARRAYFORMULA to return the column of totals.
=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))Result{4 rows × 2 cols} "Travel", 1228.5, "Meals", 173.15, …
A2:A1312 rows × 1 col: "Travel", "Meals", "Software", "Travel", …A range: 12 cells in one column.
UNIQUE(A2:A13){4 rows × 1 col} "Travel", "Meals", "Software", "Office"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1312 rows × 1 col: "Travel", "Meals", "Software", "Travel", …
- array
C2:C1312 rows × 1 col: $412.50, $38.20, $149.00, $286.00, …A range: 12 cells in one column.
UNIQUE(A2:A13){4 rows × 1 col} "Travel", "Meals", "Software", "Office"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1312 rows × 1 col: "Travel", "Meals", "Software", "Travel", …
- array
SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)){4 rows × 1 col} 1228.5, 173.15, 210, 111SUMIFS: Adds values that meet every condition.- sum_range
C2:C1312 rows × 1 col: $412.50, $38.20, $149.00, $286.00, … - criteria_range1
A2:A1312 rows × 1 col: "Travel", "Meals", "Software", "Travel", … - criteria1
UNIQUE(A2:A13){4 rows × 1 col} "Travel", "Meals", "Software", "Office"
- sum_range
HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13))){4 rows × 2 cols} "Travel", 1228.5, "Meals", 173.15, …HSTACK: Places ranges side by side.- array1
UNIQUE(A2:A13){4 rows × 1 col} "Travel", "Meals", "Software", "Office" - array2
SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)){4 rows × 1 col} 1228.5, 173.15, 210, 111
- array1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(HSTACK(UNIQUE(A2:A13),SUMIF(A2:A13,UNIQUE(A2:A13),C2:C13))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LET(c,UNIQUE(A2:A13),SORT(HSTACK(c,SUMIFS(C2:C13,A2:A13,c)),2,-1))Excel only | spills 4 × 2Different result | Name the category list once with LET and sort by the total, largest first. Sorted by total: Travel, Software, Meals, Office. |
| =QUERY(A1:C13,"select A, sum(C) group by A label sum(C) 'Total'",1)Google Sheets | spills 5 × 2Different result | In Google Sheets, QUERY groups and totals in one step, with a header row and the categories A to Z. Header row, categories A to Z. |
Common mistakes
- Blank category cells add a 0 row to UNIQUE. Use
UNIQUE(FILTER(A2:A50,A2:A50<>""))when the range runs past the data. - Microsoft 365 also has GROUPBY, which does this in one function:
=GROUPBY(A2:A13,C2:C13,SUM). It isn't in Excel 2021 or 2024.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum by category with one formula
E2, return each expense category once with the total amount for it next to it, from one formula.Questions
How do I add a grand total row?
Stack it under the table: =LET(c,UNIQUE(A2:A13),t,SUMIFS(C2:C13,A2:A13,c),VSTACK(HSTACK(c,t),HSTACK("Total",SUM(t)))).
How do I total by category for one month?
Add date criteria to SUMIFS: SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13),B2:B13,">="&H1,B2:B13,"<="&EOMONTH(H1,0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.