How to sum by category in Excel

Method 1: SUMIF against a list of categories

Type each category once in column F, then put SUMIF next to the first one and fill down. SUMIF looks through the categories in B, finds the ones equal to F2, and adds the matching amounts from C. The dollar signs keep both ranges fixed while F2 changes to F3, F4 and F5.

This works in every version of Excel, and it is the right choice when the list of categories is fixed, such as a budget with set lines, or when you want them in a particular order. SUMIF is not case-sensitive, so the Meals total includes the row typed as "meals".

If you need a second condition, such as one category in one month, switch to SUMIFS, which puts the amounts first: =SUMIFS($C$2:$C$11,$B$2:$B$11,F2,$A$2:$A$11,">="&DATE(2026,10,1)).

Check the totals: stray spaces split a category

Always compare the category totals with the grand total. Here the expenses add up to $1,425.30 but the four category totals only reach $1,128.90. The missing $296.40 is the row in B6, where "Travel " has a trailing space, so SUMIF does not treat it as Travel. Spaces like this come from imports, copy-paste and dropdown lists built from untidy data, and they are invisible in the cell.

The lasting fix is a clean column. Put =TRIM(B2) in D2 and fill down; TRIM removes leading, trailing and doubled spaces. Then point SUMIF at column D instead of B: =SUMIF($D$2:$D$11,F2,$C$2:$C$11) gives Travel its full $865.60. SUMIF cannot take TRIM(B2:B11) as its range, because it needs a real range. If you can't add a column, =SUMPRODUCT((TRIM($B$2:$B$11)=F2)*$C$2:$C$11) does the cleaning inside the formula.

Case is not the problem it seems. SUMIF, UNIQUE and PivotTables all treat "Meals" and "meals" as one category. It only matters if you need them apart, and then you need EXACT inside SUMPRODUCT.

Method 2: an automatic summary with UNIQUE and SUMIF

In Excel 365, you don't have to type the category list. UNIQUE pulls each category once from the clean column, SUMIF totals each one, and HSTACK puts the two side by side. The result spills into as many rows as there are categories, and a new category in the data gets its own row automatically.

Run UNIQUE on the trimmed column, not the original one. =UNIQUE(B2:B11) returns five categories here, because "Travel " counts as its own value. Leave the cells below and to the right of the formula empty, or it shows #SPILL!.

UNIQUE needs Excel 365 or 2021, and HSTACK needs 365 or 2024. Without HSTACK, put =UNIQUE(D2:D11) in F2 and =SUMIF(D2:D11,F2#,C2:C11) in G2; F2# refers to the whole spilled list.

Sort the summary by total

Wrap the summary in SORT to put the biggest category first. SORT's second argument is the column to sort by (2, the totals) and the third is the order (-1 for largest first). LET names the category list once, so UNIQUE runs once and the formula stays readable.

For alphabetical order instead, sort the categories before totalling them: =LET(c,SORT(UNIQUE(D2:D11)),HSTACK(c,SUMIF(D2:D11,c,C2:C11))). To add a grand total row underneath, stack it on with VSTACK: =VSTACK(summary,HSTACK("Total",SUM(C2:C11))).

Method 3: a PivotTable (no formulas)

A PivotTable builds the same summary with a few clicks and works in every version. It is the best pick when you also want to slice by month, filter, or drill into the rows behind a total by double-clicking it.

Two things catch people out. A PivotTable does not update when the data changes: right-click it and choose Refresh, or use Data > Refresh All. And it shows "Travel" and "Travel " as two separate rows, so clean the category column first, just as with the formulas.

1. Click any cell in the data and press Ctrl+T to turn it into a table, so new rows are included on refresh.
2. Go to Insert > PivotTable, choose New Worksheet and click OK.
3. Drag Category to Rows and Amount to Values.
4. If it says Count of Amount, click it, choose Value Field Settings and pick Sum. Count usually means some amounts are text or blank.
5. To sort by total, right-click any total and choose Sort > Sort Largest to Smallest.