How to Sum a Column by Its Header Name in Excel

To sum a column by its header in Excel, you let a lookup function find the column and then wrap it in SUM. Type a header name such as "Mar" in a cell, and the total switches to that column with no edits to the formula. This guide is for anyone who builds a summary or dashboard where the reader picks the month, region or product to total.

Why sum a column by its header name?#

A formula that finds its column by name keeps working when the layout changes. =SUM(D2:D6) always adds column D. If someone inserts a column or the reader wants April instead, you have to edit the formula by hand.

A header-based formula reads the column name from a cell, so one formula can answer "total for March", "total for April" and so on. It is the same idea as a two-way lookup that picks a row and a column, except that you keep every row and add them up.

All the examples use this monthly sales table in A1:E6, with the month to total in G2:

How to sum a column by header with XLOOKUP#

In Microsoft 365 or Excel 2021 and later, XLOOKUP can return a whole column, so SUM around XLOOKUP does the job:

=SUM(XLOOKUP(G2,B1:E1,B2:E6))

Here is what each part does:

• G2 is what to find: the header name, "Mar".
• B1:E1 is where to look: the header row.
• B2:E6 is what to return. It has five rows, so XLOOKUP returns all five values from the matching column.
• SUM adds those values.

With "Mar" in G2, the result is 5,250 (1,100 + 900 + 1,400 + 650 + 1,200). Type "Feb" in G2 and it changes to 5,300. XLOOKUP looks for an exact match by default and is not case-sensitive, so "mar" works too. The XLOOKUP function guide lists the optional arguments.

How to sum a column by header with INDEX and MATCH#

INDEX and MATCH do the same job in every version of Excel. MATCH finds the column number and INDEX returns that column:

=SUM(INDEX(B2:E6,0,MATCH(G2,B1:E1,0)))

1. MATCH(G2,B1:E1,0) returns 3, because "Mar" is the third header in B1:E1. The final 0 asks for an exact match. See the MATCH function guide for the other match types.
2. INDEX(B2:E6,0,3) returns the third column of the data. The 0 as the row number is the key: it means "all rows", not one cell. The INDEX function guide explains this.
3. SUM adds the column: 5,250 again.

This is the INDEX MATCH pattern with the row left open. If you have used INDEX MATCH MATCH for two-way lookups, you replace the row MATCH with 0. For a refresher on why MATCH returns a position, read how the MATCH function works.

Use the same lookup with AVERAGE, MAX or SUMIFS#

The lookup returns a range, so any function that accepts a range can use it. SUM is only the most common choice:

The SUMIFS version adds Leeds, Hull and Derby: 1,100 + 1,400 + 1,200 = 3,700. The criteria range (B2:B6) must have the same number of rows as the data range, or SUMIFS returns #VALUE!.

You can also flip the idea and total a row by its label. =SUM(XLOOKUP("Hull",A2:A6,B2:E6)) returns Hull's whole row and adds it: 5,750. The guide to SUM and AutoSum covers the basics of SUM itself.

The SUMPRODUCT alternative#

SUMPRODUCT can sum by header without a lookup function. It compares every header to G2 and multiplies the result by the data:

=SUMPRODUCT((B1:E1=G2)*B2:E6)

B1:E1=G2 gives TRUE only under "Mar". TRUE counts as 1 and FALSE as 0, so only the March column survives the multiplication. The result is 5,250.

Two differences are worth knowing:

• No match gives 0, not #N/A. That is quieter, but it can hide a typo in G2.
• Duplicate headers are added together. If two columns are both called "Mar", SUMPRODUCT adds both, while XLOOKUP and MATCH use only the first one.

Why does the sum by header return #N/A or the wrong total?#

Most problems come from the header text or from ranges that don't line up. Check these:

1. #N/A: the header doesn't exist. "March" won't match "Mar", and "Mar " with a trailing space won't either. Clean the input with TRIM, or use a drop-down list for G2 so readers can only pick real headers.
2. Dates in the header row. If B1 holds a real date shown as "Jan", the cell contains a number, not the text "Jan". Match it with a real date in G2, or use text headers.
3. Misaligned ranges. The header range and the data range must cover the same columns. B1:E1 with C2:F6 returns the column one place to the right of the one you asked for.
4. The label column inside the data. If the data range starts at column A, MATCH counts from A and the position is off by one. Start both ranges in column B.
5. A blank result instead of a total. With XLOOKUP you can set a fallback: =SUM(XLOOKUP(G2,B1:E1,B2:E6,0)) returns 0 when the header is missing.

To show which header a value sits under, rather than a total, see how to return the column header for a value.

Practise it#

The formula on this page combines three skills: a lookup, a range that returns a whole column, and SUM. Practise XLOOKUP in a live spreadsheet, then practise INDEX and MATCH for files that must work in older Excel. If SUM itself is new to you, start with the SUM and AutoSum lesson. Each lesson checks your formula as soon as you press Enter.