How to Sum by Category in Excel

To sum by category in Excel, you need two things: a list of each category once, and a SUMIF next to each name that adds up its amounts. That gives you a clean summary, such as total spend on Travel, Meals and Office, that updates whenever the data changes. This guide builds one step by step, then shows the automatic UNIQUE version, percent of total, a second condition and the PivotTable alternative.

The sample data#

Every example uses this expense list in A1:B8:

The categories repeat in no particular order, which is how real data usually looks. The finished summary should show Travel 420, Meals 105, Office 115 and Software 99, for a grand total of 739.

Step 1: List each category once#

Type each category once in a new column, starting in D2: Travel, Meals, Office, Software. Add a header such as "Category" in D1. With a short list, typing is fine.

For a longer list, copy column A to column D, select it, and use Data > Remove Duplicates. Excel deletes the repeats and leaves one of each. The catch is that this list doesn't update: if a new category appears in the data later, you have to add it yourself.

Step 2: Total each category with SUMIF#

SUMIF adds the numbers in one range when the matching cells in another range meet a condition. Here the condition is "the category equals the name in column D". In E2, type:

=SUMIF($A$2:$A$8,D2,$B$2:$B$8)

Press Enter: E2 shows 420 (120 + 300). Then fill down to E5 (Windows: Ctrl+D; Mac: ⌘D). The $ signs lock the two ranges so they don't slide down, while D2 has no $ and changes to D3, D4 and D5:

SUMIF ignores capitals, so "travel" in column D still matches "Travel". For a full walk-through of SUMIF, read how to use SUMIF, or see the SUMIF function guide.

Step 3: Check the totals add up#

Add the summary and compare it with the original column:

=SUM(E2:E5)

It should equal =SUM(B2:B8), which is 739. If the summary is smaller, some rows didn't match any category in your list. The usual causes are a missing category, a typo such as "Meal" instead of "Meals", or an extra space, like "Travel " with a trailing space. SUMIF needs an exact match, so those rows are left out without any warning. Clean the column with TRIM, fix the typos, and the totals will agree.

Build the category list automatically with UNIQUE#

In Excel 2021 or Microsoft 365, the UNIQUE function builds the list for you and updates it when the data changes. In D2, type:

=UNIQUE(A2:A8)

The result spills into the cells below: Travel, Meals, Office, Software, in the order they first appear. To sort them alphabetically, wrap it in SORT: =SORT(UNIQUE(A2:A8)).

Then point SUMIF at the whole spilled list with the # sign, which means "the full spill range starting at D2":

=SUMIF(A2:A8,D2#,B2:B8)

Type this once in E2 and it spills a total next to every category. Add a new category to the data and both columns grow on their own. If you see #SPILL!, something is blocking the cells below; clear them. The UNIQUE function guide has more examples, and older versions of Excel need the manual list from Step 1.

Percent of total and the biggest category#

Once you have the totals, a share of the total is one division away. In F2, type this and fill down, then format the column as a percentage:

=E2/SUM($E$2:$E$5)

Travel is 420 / 739, about 57% of all spending. To find the biggest category by formula, combine INDEX and MATCH with MAX: =INDEX(D2:D5,MATCH(MAX(E2:E5),E2:E5,0)) returns Travel.

Sum by category with a second condition#

When you need totals by category and something else, such as a month or a region, switch to SUMIFS. Say column C holds the expense date. Travel spending in January 2024 is:

=SUMIFS(B2:B8,A2:A8,"Travel",C2:C8,">="&DATE(2024,1,1),C2:C8,"<="&DATE(2024,1,31))

SUMIFS puts the sum range first, then a range and criteria for each condition. A grid with categories down the side and months across the top is a common report built this way; see how to sum by month and how to sum by date range. If your categories are buried inside longer descriptions, use wildcards as shown in SUMIF contains text.

Sum by category with a PivotTable#

A PivotTable gives the same totals without any formulas. Click anywhere in the data, go to Insert > PivotTable, and choose a new sheet. Drag Category to Rows and Amount to Values. Excel lists each category once and shows its sum, with a grand total at the bottom.

PivotTables are fast and easy to rearrange, but they don't update until you refresh them (right-click > Refresh). SUMIF is better for a report with a fixed layout that must always be current, while a PivotTable is better for exploring. Many job tests ask for both, so it is worth knowing each.

Practise it#

A category summary is one of the most common tasks in any Excel job. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then build a full summary table in the pivot-style summary lesson. When you're ready, try the data analyst Excel test.