# How to sum by category with one formula in Excel

Pair UNIQUE with SUMIFS: `=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))` returns each category with its total.

**Excel:** `=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(HSTACK(UNIQUE(A2:A13),SUMIF(A2:A13,UNIQUE(A2:A13),C2:C13)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Category | Date | Amount |  | Category | Total |
| Travel | 2026-04-02 | 412.5 |  |  |  |
| Meals | 2026-04-03 | 38.2 |  |  |  |
| Software | 2026-04-05 | 149 |  |  |  |
| Travel | 2026-04-08 | 286 |  |  |  |
| Meals | 2026-04-09 | 64.75 |  |  |  |
| Office | 2026-04-11 | 92.4 |  |  |  |
| Software | 2026-04-15 | 49 |  |  |  |
| Meals | 2026-04-16 | 22.9 |  |  |  |
| Travel | 2026-04-21 | 530 |  |  |  |
| Office | 2026-04-24 | 18.6 |  |  |  |
| Meals | 2026-04-28 | 47.3 |  |  |  |
| Software | 2026-04-30 | 12 |  |  |  |

Formula in E2: `=HSTACK(UNIQUE(A2:A13),SUMIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))`

Result (spills 4 × 2): Travel, $1,228.50; Meals, $173.15; Software, $210.00; Office, $111.00

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

## Other ways

- `=LET(c,UNIQUE(A2:A13),SORT(HSTACK(c,SUMIFS(C2:C13,A2:A13,c)),2,-1))`: 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)`: 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.

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

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/sum-by-category-with-one-formula