How to sum a matching row across several columns in Excel

Q: How do I average the row instead?

Use AVERAGE: =AVERAGE(XLOOKUP(H1,A2:A7,B2:E7)).

Q: How do I add only some quarters?

Filter by the header: =SUMPRODUCT((A2:A7=H1)*((B1:E1="Q2")+(B1:E1="Q3"))*B2:E7).

Formula (Excel and Google Sheets)

=SUM(XLOOKUP(H1,A2:A7,B2:E7))

Return the whole row with XLOOKUP and add it up: =SUM(XLOOKUP(H1,A2:A7,B2:E7)).

Result on the example: $235,150 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

With a four-column return range, XLOOKUP returns Cara's four quarters as one row. SUM adds them: $57,300 + $54,800 + $60,150 + $62,900 = $235,150.

=SUM(XLOOKUP(H1,A2:A7,B2:E7))

Result235150

H1"Cara"
The value in H1.
A2:A76 rows × 1 col: "Ana", "Ben", "Cara", "Dev", …
A range: 6 cells in one column.
B2:E76 rows × 4 cols: $48,200, $51,300, $49,800, $55,100, …
A range: 6 rows × 4 columns.
XLOOKUP(H1,A2:A7,B2:E7){1 row × 4 cols} 57300, 54800, 60150, 62900
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueH1"Cara"
- lookup_arrayA2:A76 rows × 1 col: "Ana", "Ben", "Cara", "Dev", …
- return_arrayB2:E76 rows × 4 cols: $48,200, $51,300, $49,800, $55,100, …
SUM(XLOOKUP(H1,A2:A7,B2:E7))235150
SUM: Adds numbers and ranges. Skips text and blanks.
- number1XLOOKUP(H1,A2:A7,B2:E7){1 row × 4 cols} 57300, 54800, 60150, 62900

In Google Sheets

The same formula works in Google Sheets: =SUM(XLOOKUP(H1,A2:A7,B2:E7)). We ran it in Google Sheets mode on the same data and it returns $235,150.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT((A2:A7=H1)*B2:E7)	$235,150	Every version: the test is 1 on Cara's row, so only her quarters survive the multiplication. If the name appears twice, both rows are added.
=SUM(INDEX(B2:E7,MATCH(H1,A2:A7,0),0))	$235,150	INDEX with column 0 returns the whole row.

Common mistakes

VLOOKUP returns one column at a time; adding four VLOOKUPs works but breaks when columns move.
SUMIF can't do this: its sum range is one column, so SUMIF(A2:A7,H1,B2:E7) adds only Q1.

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 matching row across several columns

In H2, return the full-year total (Q1 to Q4) for the rep in H1.

Questions

How do I average the row instead?

Use AVERAGE: =AVERAGE(XLOOKUP(H1,A2:A7,B2:E7)).

How do I add only some quarters?

Filter by the header: =SUMPRODUCT((A2:A7=H1)*((B1:E1="Q2")+(B1:E1="Q3"))*B2:E7).

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.