# How to sum by a category that is stored in another table in Excel

Sum each product, then keep the products in the category: `=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))`.

**Excel:** `=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Product | Sales |  | Product | Category |  | Category | Accessories |
| Mouse | 420 |  | Laptop | Computers |  | Total |  |
| Laptop | 1840 |  | Mouse | Accessories |  |  |  |
| Dock | 210 |  | Dock | Accessories |  |  |  |
| Monitor | 395 |  | Monitor | Displays |  |  |  |
| Mouse | 265 |  | Keyboard | Accessories |  |  |  |
| Keyboard | 180 |  |  |  |  |  |  |
| Laptop | 2100 |  |  |  |  |  |  |
| Dock | 185 |  |  |  |  |  |  |

Formula in H2: `=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))`

Result: $1,260

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

## Other ways

- `=SUMPRODUCT((XLOOKUP(A2:A9,D2:D6,E2:E6)=H1)*B2:B9)`: 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.

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

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-from-another-table