# How to sum a matching row across several columns in Excel

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

**Excel:** `=SUM(XLOOKUP(H1,A2:A7,B2:E7))` (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 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Rep | Q1 | Q2 | Q3 | Q4 |  | Rep | Cara |
| Ana | 48200 | 51300 | 49800 | 55100 |  | Year total |  |
| Ben | 61750 | 58900 | 63400 | 66200 |  |  |  |
| Cara | 57300 | 54800 | 60150 | 62900 |  |  |  |
| Dev | 39800 | 42100 | 44600 | 47300 |  |  |  |
| Eli | 52400 | 50750 | 53900 | 56800 |  |  |  |
| Fay | 44150 | 46900 | 45200 | 49750 |  |  |  |

Formula in H2: `=SUM(XLOOKUP(H1,A2:A7,B2:E7))`

Result: $235,150

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

## Other ways

- `=SUMPRODUCT((A2:A7=H1)*B2:E7)`: 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))`: 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.

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

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