# How to sum a column based on its header in Excel

Look up the column by its header and sum what comes back: `=SUM(XLOOKUP(J1,B1:G1,B2:G6))`.

**Excel:** `=SUM(XLOOKUP(J1,B1:G1,B2:G6))` (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 | J |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Store | Jan | Feb | Mar | Apr | May | Jun |  | Month | Apr |
| Downtown | 18450 | 17920 | 19630 | 20110 | 21480 | 22950 |  | Total |  |
| Airport | 24310 | 22870 | 25140 | 26020 | 27390 | 28810 |  |  |  |
| Mall | 15780 | 16240 | 17010 | 16480 | 18230 | 19640 |  |  |  |
| Harbor | 11960 | 12430 | 13250 | 14070 | 15120 | 16380 |  |  |  |
| Uptown | 20540 | 19870 | 21360 | 22410 | 23050 | 24570 |  |  |  |

Formula in J2: `=SUM(XLOOKUP(J1,B1:G1,B2:G6))`

Result: $99,090

## How it works

XLOOKUP finds "Apr" in the header row `B1:G1` and, because the return range `B2:G6` has several rows, returns the whole column under it. SUM adds that column. Change J1 to another month and the total switches columns.

## Other ways

- `=SUM(INDEX(B2:G6,0,MATCH(J1,B1:G1,0)))`: Works in every version: a row number of 0 makes INDEX return the entire column.
- `=SUMPRODUCT((B1:G1=J1)*B2:G6)`: Multiplies the grid by a TRUE/FALSE header test; also handles two columns with the same header.

## Common mistakes

- If J1 has a trailing space or the header is a date formatted as "Apr", the lookup fails with #N/A. Match like with like.
- The return range must start in the same column as the header range, or the result is shifted by a column.

## Questions

**How do I sum a column by header with a condition on rows too?**

Use the column as SUMIFS's sum range: `=SUMIFS(XLOOKUP(J1,B1:G1,B2:G6),A2:A6,"Mall")`.

**How do I sum a row by its label instead?**

Swap the lookup direction: `=SUM(XLOOKUP("Mall",A2:A6,B2:G6))`.

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