How to sum by a category that is stored in another table in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))Sum each product, then keep the products in the category: =SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1)).
- Result on the example
- $1,260 in
H2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIFS(B2:B9,A2:A9,D2:D6) returns one total per product in the category table. Multiplying by E2:E6=H1 keeps the totals of the accessory products and zeros the others, and SUMPRODUCT adds what is left. No helper column is needed in the sales table.
=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))Result1260
B2:B98 rows × 1 col: $420, $1,840, $210, $395, …A range: 8 cells in one column.
A2:A98 rows × 1 col: "Mouse", "Laptop", "Dock", "Monitor", …A range: 8 cells in one column.
D2:D65 rows × 1 col: "Laptop", "Mouse", "Dock", "Monitor", …A range: 5 cells in one column.
SUMIFS(B2:B9,A2:A9,D2:D6){5 rows × 1 col} 3940, 685, 395, 395, …SUMIFS: Adds values that meet every condition.- sum_range
B2:B98 rows × 1 col: $420, $1,840, $210, $395, … - criteria_range1
A2:A98 rows × 1 col: "Mouse", "Laptop", "Dock", "Monitor", … - criteria1
D2:D65 rows × 1 col: "Laptop", "Mouse", "Dock", "Monitor", …
- sum_range
E2:E65 rows × 1 col: "Computers", "Accessories", "Accessories", "Displays", …A range: 5 cells in one column.
H1"Accessories"The value in H1.
E2:E6=H1{5 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1{5 rows × 1 col} 0, 685, 395, 0, …The * operator multiplies.
SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))1260SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1{5 rows × 1 col} 0, 685, 395, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1)). We ran it in Google Sheets mode on the same data and it returns $1,260.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((XLOOKUP(A2:A9,D2:D6,E2:E6)=H1)*B2:B9)Excel only | $1,260 | Excel 365: looks up each sale's category and keeps the matching rows. |
Common mistakes
- A product sold but missing from the category table is left out silently. Check with
=COUNTIF(D2:D6,A2)on the sales rows. - If a product appears twice in the category table, its sales are counted twice.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum by a category that is stored in another table
H2, total the sales for every product whose category (from the table in D2:E6) is the one in H1.Questions
Is a helper column simpler?
Often, yes: add =XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6) next to each sale, then use a plain SUMIF on that column.
How do I total every category at once?
Put the categories in G2:G4 and fill =SUMPRODUCT(SUMIFS($B$2:$B$9,$A$2:$A$9,$D$2:$D$6)*($E$2:$E$6=G2)) down.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.