How to sum a column based on its header in Excel
Formula (Excel and Google Sheets)
=SUM(XLOOKUP(J1,B1:G1,B2:G6))Look up the column by its header and sum what comes back: =SUM(XLOOKUP(J1,B1:G1,B2:G6)).
- Result on the example
- $99,090 in
J2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in J2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUM(XLOOKUP(J1,B1:G1,B2:G6))Result99090
J1"Apr"The value in J1.
B1:G11 row × 6 cols: "Jan", "Feb", "Mar", "Apr", …A range: 6 cells in one row.
B2:G65 rows × 6 cols: $18,450, $17,920, $19,630, $20,110, …A range: 5 rows × 6 columns.
XLOOKUP(J1,B1:G1,B2:G6){5 rows × 1 col} 20110, 26020, 16480, 14070, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
J1"Apr" - lookup_array
B1:G11 row × 6 cols: "Jan", "Feb", "Mar", "Apr", … - return_array
B2:G65 rows × 6 cols: $18,450, $17,920, $19,630, $20,110, …
- lookup_value
SUM(XLOOKUP(J1,B1:G1,B2:G6))99090SUM: Adds numbers and ranges. Skips text and blanks.- number1
XLOOKUP(J1,B1:G1,B2:G6){5 rows × 1 col} 20110, 26020, 16480, 14070, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(XLOOKUP(J1,B1:G1,B2:G6)). We ran it in Google Sheets mode on the same data and it returns $99,090.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(INDEX(B2:G6,0,MATCH(J1,B1:G1,0))) | $99,090 | Works in every version: a row number of 0 makes INDEX return the entire column. |
| =SUMPRODUCT((B1:G1=J1)*B2:G6) | $99,090 | 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.
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 column based on its header
J2, total the column whose header matches the month in J1.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)).
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.