How to sum a row based on a lookup value in Excel
Formula (Excel and Google Sheets)
=SUM(XLOOKUP(H1,A2:A6,B2:E6))Look up the row and sum the result: =SUM(XLOOKUP(H1,A2:A6,B2:E6)) returns the whole matching row and adds it.
- Result on the example
- 4,070 in
H2 - Works in
- Excel 2021, 2024 and Microsoft 365
- 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
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.
=SUM(XLOOKUP(H1,A2:A6,B2:E6))Result4070
H1"Trail shoe"The value in H1.
A2:A65 rows × 1 col: "Road shoe", "Trail shoe", "Spike", "Sandal", …A range: 5 cells in one column.
B2:E65 rows × 4 cols: 1,240, 1,385, 1,510, 1,320, …A range: 5 rows × 4 columns.
XLOOKUP(H1,A2:A6,B2:E6){1 row × 4 cols} 860, 1045, 1190, 975XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
H1"Trail shoe" - lookup_array
A2:A65 rows × 1 col: "Road shoe", "Trail shoe", "Spike", "Sandal", … - return_array
B2:E65 rows × 4 cols: 1,240, 1,385, 1,510, 1,320, …
- lookup_value
SUM(XLOOKUP(H1,A2:A6,B2:E6))4070SUM: Adds numbers and ranges. Skips text and blanks.- number1
XLOOKUP(H1,A2:A6,B2:E6){1 row × 4 cols} 860, 1045, 1190, 975
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(XLOOKUP(H1,A2:A6,B2:E6)). We ran it in Google Sheets mode on the same data and it returns 4,070.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(INDEX(B2:E6,MATCH(H1,A2:A6,0),0)) | 4,070 | Every version: a column number of 0 makes INDEX return the whole row. |
| =SUMPRODUCT((A2:A6=H1)*B2:E6) | 4,070 | 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.
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 a row based on a lookup value
H2, total the four quarters for the product in H1.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.
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.