How to total each category with one formula in Excel
Excel formula
=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))List the categories with UNIQUE and sum each with SUMIF, side by side: =HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9)).
- 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 returns each category once. Handing that list to SUMIF as the condition returns one total per category, in the same order. HSTACK puts the two lists side by side, and the result spills into a two-column summary that grows when a new category appears.
=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))Result{4 rows × 2 cols} "Rent", 1800, "Groceries", 357.72, …
B2:B98 rows × 1 col: "Rent", "Groceries", "Fuel", "Groceries", …A range: 8 cells in one column.
UNIQUE(B2:B9){4 rows × 1 col} "Rent", "Groceries", "Fuel", "Dining"UNIQUE: Distinct rows of a range. Spills.- array
B2:B98 rows × 1 col: "Rent", "Groceries", "Fuel", "Groceries", …
- array
UNIQUE(B2:B9){4 rows × 1 col} "Rent", "Groceries", "Fuel", "Dining"UNIQUE: Distinct rows of a range. Spills.- array
B2:B98 rows × 1 col: "Rent", "Groceries", "Fuel", "Groceries", …
- array
C2:C98 rows × 1 col: $1,800.00, $142.67, $58.40, $96.15, …A range: 8 cells in one column.
SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9){4 rows × 1 col} 1800, 357.72, 119.6, 107.25SUMIF: Adds values where the range meets a condition.- range
B2:B98 rows × 1 col: "Rent", "Groceries", "Fuel", "Groceries", … - criteria
UNIQUE(B2:B9){4 rows × 1 col} "Rent", "Groceries", "Fuel", "Dining" - sum_range
C2:C98 rows × 1 col: $1,800.00, $142.67, $58.40, $96.15, …
- range
HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9)){4 rows × 2 cols} "Rent", 1800, "Groceries", 357.72, …HSTACK: Places ranges side by side.- array1
UNIQUE(B2:B9){4 rows × 1 col} "Rent", "Groceries", "Fuel", "Dining" - array2
SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9){4 rows × 1 col} 1800, 357.72, 119.6, 107.25
- array1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))). 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(B2:B9),HSTACK(c,SUMIF(B2:B9,c,C2:C9))) | spills 4 × 2 | LET names the category list once, so UNIQUE runs only once and the formula is easier to read. |
Common mistakes
- Anything in the spill area (E2:F5 here) blocks the result with #SPILL!.
- Blank rows inside the range show up as a 0 category. Point the ranges at the data only, or filter out blanks first.
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.
Total each category with one formula
E2, write one formula that lists each category once with its total next to it.Questions
Is there a function that does this directly?
Excel 365 has GROUPBY: =GROUPBY(B2:B9,C2:C9,SUM) returns the same summary, sorted, with a grand total row. In Google Sheets, QUERY with group by does the same.
How do I sort the summary by total?
Wrap it in SORT: =SORT(HSTACK(…),2,-1) in Excel, or SORT(…,2,FALSE) in Google Sheets.
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.