How to sum by category in Excel and Google Sheets

Step by step

1. 1Lay out the data with a category column (A) and a value column (B).
2. 2Sum one category: =SUMIF(A2:A100,"Fruit",B2:B100) — range to test, criterion, range to sum.
3. 3Point the criterion at a cell instead of hard-coding it: =SUMIF($A$2:$A$100,D2,$B$2:$B$100), with category names listed in column D — copy down for a reusable summary table.
4. 4Generate the category list automatically: in Sheets or Excel 365, =UNIQUE(A2:A100) spills every distinct category; put the SUMIF beside it and the summary maintains itself as new categories appear.
5. 5Add more conditions with SUMIFS: =SUMIFS(C2:C100,A2:A100,"Fruit",B2:B100,"Jan") totals rows that match category AND month. Note SUMIFS takes the sum range FIRST.

Tips

• One-formula summary in Sheets: =QUERY(A1:B100,"select A, sum(B) where A is not null group by A",1) returns the whole category breakdown, sorted and labelled.
• Sum several categories at once: =SUMPRODUCT((A2:A100="Fruit")+(A2:A100="Dairy"),B2:B100) adds rows matching either label.
• SUMIF criteria are case-insensitive ("fruit" = "Fruit" = "FRUIT") and accept wildcards: "Fr*" matches every category starting with Fr.
• Keep the criteria ranges absolute ($A$2:$A$100) and the criterion cell relative (D2) so the formula fills down cleanly.
• For large datasets a PivotTable (Insert → PivotTable / Insert → Pivot table) does the same grouping with drag-and-drop and refreshes on demand — use formulas when you need live results embedded in a report.
• Totals check: =SUM(B2:B100) should equal the sum of your per-category cells — a quick way to catch a category typo.

Frequently asked