How to sum a column by its header name in Excel
Formula (Excel and Google Sheets)
=SUM(XLOOKUP(I1,B1:F1,B2:F7))Return the column with a horizontal XLOOKUP and add it up: =SUM(XLOOKUP(I1,B1:F1,B2:F7)).
- Result on the example
- $22,075 in
I2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in I2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUM(XLOOKUP(I1,B1:F1,B2:F7))Result22075
I1"Marketing"The value in I1.
B1:F11 row × 5 cols: "Rent", "Utilities", "Payroll", "Marketing", …A range: 5 cells in one row.
B2:F76 rows × 5 cols: $8,500, $1,240, $42,100, $3,150, …A range: 6 rows × 5 columns.
XLOOKUP(I1,B1:F1,B2:F7){6 rows × 1 col} 3150, 2875, 4020, 3640, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
I1"Marketing" - lookup_array
B1:F11 row × 5 cols: "Rent", "Utilities", "Payroll", "Marketing", … - return_array
B2:F76 rows × 5 cols: $8,500, $1,240, $42,100, $3,150, …
- lookup_value
SUM(XLOOKUP(I1,B1:F1,B2:F7))22075SUM: Adds numbers and ranges. Skips text and blanks.- number1
XLOOKUP(I1,B1:F1,B2:F7){6 rows × 1 col} 3150, 2875, 4020, 3640, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(XLOOKUP(I1,B1:F1,B2:F7)). We ran it in Google Sheets mode on the same data and it returns $22,075.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B1:F1=I1)*B2:F7) | $22,075 | Works in every version: the header test zeroes out every other column. |
| =SUM(INDEX(B2:F7,0,MATCH(I1,B1:F1,0))) | $22,075 | 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.
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 by its header name
I2, total the column whose header matches the category in I1.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)).
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.