# How to total each category with one formula in Excel

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))`.

**Excel:** `=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Category | Amount |  | Category | Total |
| 2026-04-01 | Rent | 1800 |  |  |  |
| 2026-04-03 | Groceries | 142.67 |  |  |  |
| 2026-04-07 | Fuel | 58.4 |  |  |  |
| 2026-04-10 | Groceries | 96.15 |  |  |  |
| 2026-04-14 | Dining | 64.5 |  |  |  |
| 2026-04-18 | Fuel | 61.2 |  |  |  |
| 2026-04-22 | Groceries | 118.9 |  |  |  |
| 2026-04-26 | Dining | 42.75 |  |  |  |

Formula in E2: `=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))`

Result (spills 4 × 2): Rent, $1,800.00; Groceries, $357.72; Fuel, $119.60; Dining, $107.25

## 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.

## Other ways

- `=LET(c,UNIQUE(B2:B9),HSTACK(c,SUMIF(B2:B9,c,C2:C9)))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/total-each-category-with-one-formula