# How to sum a row based on a lookup value in Excel

Look up the row and sum the result: `=SUM(XLOOKUP(H1,A2:A6,B2:E6))` returns the whole matching row and adds it.

**Excel:** `=SUM(XLOOKUP(H1,A2:A6,B2:E6))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Product | Q1 | Q2 | Q3 | Q4 |  | Product | Trail shoe |
| Road shoe | 1240 | 1385 | 1510 | 1320 |  | Units this year |  |
| Trail shoe | 860 | 1045 | 1190 | 975 |  |  |  |
| Spike | 310 | 420 | 385 | 190 |  |  |  |
| Sandal | 150 | 640 | 910 | 280 |  |  |  |
| Boot | 720 | 380 | 295 | 1060 |  |  |  |

Formula in H2: `=SUM(XLOOKUP(H1,A2:A6,B2:E6))`

Result: 4,070

## How it works

XLOOKUP finds the product in `A2:A6`. Because the return range `B2:E6` is four columns wide, it returns all four quarters of that row, and SUM adds them.

## Other ways

- `=SUM(INDEX(B2:E6,MATCH(H1,A2:A6,0),0))`: Every version: a column number of 0 makes INDEX return the whole row.
- `=SUMPRODUCT((A2:A6=H1)*B2:E6)`: Adds every row with that label, so it also works when a product appears on several rows.

## Common mistakes

- XLOOKUP and INDEX/MATCH return only the first matching row. If the label repeats, use SUMPRODUCT or SUMIF per column.
- `=SUMIF(A2:A6,H1,B2:E6)` doesn't sum the row: SUMIF resizes the sum range to one column and returns Q1 only.

## Questions

**How do I sum only some of the quarters?**

Narrow the return range: `=SUM(XLOOKUP(H1,A2:A6,B2:C6))` adds Q1 and Q2.

**What if the product is not found?**

Give XLOOKUP a fallback: `=SUM(XLOOKUP(H1,A2:A6,B2:E6,0))` returns 0.

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-a-row-based-on-a-lookup-value