How to sum a column or multiple columns based on header name in Excel?
When working with large data sets in Excel, you may frequently need to quickly sum all values under a specific header, such as summing all figures for the month of "Mar". This task can seem tedious when dealing with tables that have many columns or when the headers change regularly. Accurately summing values based on dynamic header names helps streamline reporting and ensures calculations remain correct even as data evolves. This article introduces several practical methods to efficiently sum a column or multiple columns according to their header names, making your workflow in Excel more convenient and reliable regardless of table complexity.
Sum a column or multiple columns based on header name with formula
Easily sum a column or multiple columns based on header name with an amazing tool
Pivot Table method: Sum columns by header dynamically
Sum a column or multiple columns based on header name with formula
Excel’s built-in functions allow you to sum columns based on header names without manual searching. This approach is useful when you want quick, formula-driven results and your data structure stays consistent.
Sum a column based on header name
1. Select a blank cell where you want the sum result to appear. Enter the following formula to dynamically sum a single column according to a target header name.
=SUM(INDEX(B2:I7,0,MATCH(B9,B1:I1,0)))After entering the formula, press Enter. The sum of the column that matches the header (specified in cell B9) is returned.
Parameter explanation and tips:
- B2:I7 — This is the range that contains the numerical data you want to sum.
- B1:I1 — This is the header row containing the possible column names.
- B9 — This cell holds the column header for which you want the sum (can be changed for different queries).
This formula is particularly suitable for tables where each column represents a metric (such as monthly sales), and you need to retrieve totals for a variable chosen period.
Sum two or more columns based on header name
If your analysis requires summing multiple columns sharing the same header (e.g., repeated months or categories), you can use the following formula. This is especially useful in datasets where column names are not unique or are briefly repeated:
=SUMPRODUCT((B1:I1=B9)*B2:I7)After entering the formula, press Enter. Excel will calculate the sum for all columns whose header matches the value in B9.
Parameter explanation and tips:
- B1:I1=B9 — This logical part identifies columns where the header matches cell B9.
- *B2:I7 — Multiplies the logic test by the data range, forcing unmatched columns to zero and summing only the relevant columns.
Easily sum a column or multiple columns based on header name with an amazing tool
To further simplify the summing process, especially for those less familiar with complex formulas, Kutools for Excel offers a dedicated "LOOKUP and Sum" feature. This intuitive tool allows you to quickly sum columns based on header names through a graphical interface. It’s ideal for users who want results in a few clicks without being concerned about formula accuracy or changing table structures.
1. First, click Kutools > Super LOOKUP > LOOKUP and Sum on the Excel toolbar.
2. In the LOOKUP and Sum dialog box, complete the steps below:
- 2.1 Select Lookup and sum matched value(s) in column(s) from the Lookup and Sum Type options.
- 2.2 Define the lookup value, output location, and select the relevant data range in the Select Range area.
- 2.3 In Options, you can choose:
- Return the sum of the first matched value — sums the first column only that matches the header.
- Return the sum of all matched values — sums all columns sharing the header.
- 2.4 Confirm all settings, then click OK to perform the calculation.
The results are instantly shown in your chosen output cell. Notably, Kutools automatically inserts a dynamic formula, ensuring the answer updates if the source data changes. This approach is suitable for users who frequently analyze different columns or large datasets without revisiting formulas each time.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Pivot Table method: Sum columns by header dynamically
Another widely used approach is to utilize Excel’s Pivot Table feature, especially useful when working with shifting data sets or requiring frequent re-aggregation of data. Pivot Tables allow you to dynamically select the headers you wish to sum and visualize results interactively, without writing formulas or code.
Applicable scenario: Most suitable for users analyzing large ranges or summarizing data by multiple headers/types at once.
Pros: No formulas required, offers drag-and-drop customization and automatic updating.
Cons: Does not write the sum directly to a fixed cell without manual extraction, and may require some learning for first-time users.
- Select any cell within your data range.
- Go to Insert > PivotTable. In the dialog, confirm the table/range and select a location for your Pivot Table (new/existing worksheet).
- In the Pivot Table Field List, drag the header you wish to sum (e.g., "Mar") into the Values area. If you have multiple headers to sum, drag and drop each as needed. You can use the Columns field for comparison by period/category, and the Values area will automatically total the respective columns.
- By default, Pivot Tables use "Sum" as the aggregation type. If not, click on the drop-down in the Value field, select Value Field Settings, and choose "Sum".
- After setting up, the Pivot Table will display the dynamic sum results per header.
Practical tips: You can automatically refresh a Pivot Table when the source data is updated by right-clicking anywhere inside it and selecting Refresh. Be sure to verify that your source data is accurate and consistent before refreshing.
Related articles
Sum values between two dates range in Excel
When there are two lists in your worksheet, one is the list of dates, the other is the list of the values. And you want to sum up the values between two dates range only, for instance, sum up the values between3/4/2014 and5/10/2014, how can you calculate them quickly? Here this article introduces a formula for you to sum up them in Excel.
Sum values based on criteria in another column in Excel
Sometimes you want to sum the values based on criteria in another column, for instance, here I only want to sum up the "Sale Volume" column where the corresponding "Product" column equals "A" as show as below, how can you do it? Of course, you can sum them one by one, but here I introduce some simple methods for you to sum the values in Excel.
Use vlookup and sum in Excel
Using vlookup and sum function helps you quickly find out the specified criteria and sum the corresponding values at the same time. This article showa you how to combine the vlookup and sum in the formula and then use it to vlookup and sum all matches in columns or rows.
Sum multiple columns based on single criteria in Excel
In Excel, you may always need to sum multiple columns based on one criteria. For example, you have a range of date cells, and you want to get the total values of KTE in three months - Jan, Feb and Mar, How can you do? The methods in this article will do you a favor.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in