# How to sum a column by its header name in Excel

Return the column with a horizontal XLOOKUP and add it up: `=SUM(XLOOKUP(I1,B1:F1,B2:F7))`.

**Excel:** `=SUM(XLOOKUP(I1,B1:F1,B2:F7))` (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 | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Month | Rent | Utilities | Payroll | Marketing | Travel |  | Category | Marketing |
| Jan | 8500 | 1240 | 42100 | 3150 | 1890 |  | Total |  |
| Feb | 8500 | 1315 | 42100 | 2875 | 2240 |  |  |  |
| Mar | 8500 | 1190 | 43350 | 4020 | 1560 |  |  |  |
| Apr | 8750 | 1085 | 43350 | 3640 | 2975 |  |  |  |
| May | 8750 | 990 | 43350 | 3980 | 1730 |  |  |  |
| Jun | 8750 | 1035 | 44600 | 4410 | 2120 |  |  |  |

Formula in I2: `=SUM(XLOOKUP(I1,B1:F1,B2:F7))`

Result: $22,075

## How it works

XLOOKUP searches the header row `B1:F1` for Marketing and returns the matching column of `B2:F7`, all six months. SUM adds them: $22,075. Change `I1` and the total follows, and inserting columns doesn't break it.

## Other ways

- `=SUMPRODUCT((B1:F1=I1)*B2:F7)`: Works in every version: the header test zeroes out every other column.
- `=SUM(INDEX(B2:F7,0,MATCH(I1,B1:F1,0)))`: INDEX with row 0 returns the whole column that MATCH found.

## Common mistakes

- SUMIF can't pick a column: its condition works down rows, not across headers.
- The header must match exactly. "Marketing " with a space returns #N/A.

## Questions

**How do I sum a header for some months only?**

Add a row test: `=SUMPRODUCT((B1:F1=I1)*(A2:A7="Mar")*B2:F7)`.

**How do I average the column instead?**

Swap SUM for AVERAGE: `=AVERAGE(XLOOKUP(I1,B1:F1,B2:F7))`.

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-column-by-header