Excel Sum a Row Based on a Column Header with SUMIF

Formulas & functions · Updated November 18, 2026

To sum values across a row when their column headers match a label, use SUMIF: with headers in B1:G1, row values in B2:G2, and the header to match in I1, enter =SUMIF($B$1:$G$1,I$1,$B2:$G2). Excel adds the values in row 2 under every matching header, so duplicate header names are included too.

How a horizontal SUMIF works

SUMIF is often introduced as a way to add values in a vertical list when neighboring labels meet a condition. The same function works horizontally: the criteria range can be a row of headers, and the sum range can be the corresponding row of values. Both ranges should cover the same number of cells in the same order.

The formula has three parts: the cells Excel checks, the condition it checks for, and the cells it adds. In the example, B1:G1 contains labels such as months, B2:G2 contains the amounts for one record, and I1 contains the month selected by the user. The dollar signs keep the header range fixed when the formula is copied; the row reference in the sum range can change as the formula is filled down.

For example, suppose B1:G1 contains Jan, Feb, Mar, Jan, Feb, Mar, and B2:G2 contains 120, 150, 90, 80, 110, 70. With Jan in I1, the result is 200: Excel adds 120 and 80 because both columns have a Jan header. The function returns a total, not the position of the matching column or just the first matching cell.

This method is useful for summary rows, repeated period labels, survey responses, and reports where the same category appears in more than one column. It is available in Microsoft Excel for Microsoft 365 on Windows; the same formula syntax also works in current Excel for Mac and Excel for the web.

Build the basic formula

First identify the exact header row and the value row you want to total. If the header labels are in B1:G1, the corresponding values for a record are in B2:G2, and I1 contains Jan, enter this formula in J2:

With headers in B1:G1, values in B2:G2, and the requested label in I1, use =SUMIF($B$1:$G$1,I$1,$B2:$G2). For the sample values 120, 150, 90, 80, 110, and 70 with Jan in I1, the result is 200.

The ranges are paired cell by cell: B1 is tested against I1 and, if it matches, B2 is included; C1 is tested and can include C2, and so on. The formula does not require the headers to be sorted or next to one another. It checks the entire specified range.

  1. Put the label you want to match in a cell, such as I1. It can be typed directly or chosen from a drop-down list.
  2. Enter the formula in the cell where you want the total to appear.
  3. Check that the header and value ranges line up and have equal widths.
  4. Change the label in I1 to calculate a total for another header.

You can also put a label directly in the formula, but a cell reference is usually easier to update and reuse. If Excel displays a formula error after entry, check whether your Excel regional settings expect semicolons rather than commas between arguments. The function and range logic are unchanged; the separator depends on the installation’s settings.

Copy the formula for other rows or headers

To calculate a total for each record, copy the formula down. In the example, the header range $B$1:$G$1 stays fixed, while $B2:$G2 becomes $B3:$G3 on the next row. That is why the formula uses dollar signs on the header range but not on the value row number.

To create totals for several requested headers across columns, put the selected labels in I1, J1, K1, and so on, then copy the formula to the right. The reference I$1 changes to J$1 and K$1, while its row remains 1. The value range’s row also changes when you copy down. This combination of relative and absolute references lets one formula fill a summary grid without manually editing each range.

For a small sheet, select the formula cell and use the fill handle at its lower-right corner to copy it. Before filling a large area, confirm the first copied result against a manual addition. A reference that shifts one column too far can produce a plausible-looking but incorrect total.

Duplicate headers, wildcards, and exact matches

Duplicate headers are not an error. A normal SUMIF adds every corresponding value whose header satisfies the condition. That behavior is helpful when a report has repeated month names across separate years or repeated categories in different sections. If you intended to total only one particular column, the header alone is not enough to identify it; use a more specific label or another condition.

Text criteria in SUMIF are generally not case-sensitive, so Jan and JAN are treated as a match. A criterion such as Jan normally matches that text label rather than a longer label such as January Actual. Wildcards let you match text that contains or begins with a pattern. An asterisk stands for any number of characters, and a question mark stands for one character.

With headers in B1:G1 named Jan Actual, Feb Actual, Jan Budget, Feb Budget, Jan Revised, and Feb Revised, and values in B2:G2 of 120, 150, 80, 90, 40, and 50, this formula sums all headers containing Jan:

With those headers in B1:G1 and amounts in B2:G2, enter =SUMIF($B$1:$G$1,"*Jan*",$B2:$G2). It returns 240, from 120 + 80 + 40.

Use wildcards only when a partial match is intended. A label such as Jan may match more columns than expected if the header text includes additional words. If an asterisk or question mark should be treated as a literal character, precede it in the criterion with a tilde, Excel’s wildcard escape character.

Headers that contain dates or numbers

Headers that look like dates can be stored as actual Excel dates or as text. Those are different kinds of values: a displayed label such as Jan may be a formatted date underneath, or it may simply be the three text characters Jan. A criterion must be compatible with the underlying header values, not just with how they appear on screen. If an exact match returns zero, inspect the cell’s value and number format.

When the header cells contain real dates and you want a whole month, use a lower date boundary and an exclusive upper boundary. For example, with actual dates in B1:G1 and corresponding amounts in B2:G2, the following totals dates in January 2026, including any day within that month:

With actual Excel dates in B1:G1 and amounts in B2:G2, use =SUMIFS($B2:$G2,$B$1:$G$1,">="&DATE(2026,1,1),$B$1:$G$1,"<"&DATE(2026,2,1)). If the January columns contain 120 and 80, the result is 200.

This uses SUMIFS, which takes the sum range first, followed by pairs of criteria ranges and criteria. The upper boundary is February 1 rather than January 31, so the formula works even if a header includes a time as well as a date. If the headers are text month names, use a text criterion instead; date comparisons do not convert arbitrary text into dates automatically.

Choosing between SUMIF and other approaches

For one condition across one header row, SUMIF is usually the clearest option. It directly expresses the task and is easier to audit than a more elaborate formula. If you need conditions on more than one aligned header row—for example, matching both a month and a department—SUMIFS can apply multiple criteria ranges while adding the same row of values.

Another option is SUMPRODUCT, which can be useful when calculations need array logic that is awkward to express with SUMIF. For a simple header match, though, it is not automatically better. It can be harder for a colleague to understand, and the ranges still need to align. If the goal is to return the value under one unique header rather than add every match, a lookup function may be more appropriate; a lookup commonly returns one matching result instead of summing duplicates.

Choose based on the intended result:

  • Use SUMIF when one row of headers determines which cells in one value row to add.
  • Use SUMIFS when the total depends on multiple conditions.
  • Use a lookup when you want a value from a matching column and expect one relevant match.
  • Use a PivotTable when you need a reusable summary across many records and categories rather than a single formula result.

These approaches solve related but different problems. In particular, a lookup is not a substitute for summing duplicate headers unless the data guarantees that only one matching column exists.

Common errors and ways to check the result

A zero result usually means Excel did not find a qualifying header, or that the corresponding sum cells do not contain numbers. Look for extra spaces, inconsistent punctuation, text dates, or a criterion that points to the wrong cell. Two labels can look identical on screen but differ because one contains a trailing space or a nonbreaking space copied from another source.

A result that is too large often means a header appears more than once. Since SUMIF adds all matches, duplicate labels contribute multiple amounts. If duplicate columns represent different years or scenarios, add that distinction to the criteria or make the headers unique. A result that is too small can indicate that the selected ranges omit columns at one end.

Before relying on a total, check these details:

  • The criteria range covers the intended header cells, and the sum range covers their corresponding values.
  • Both ranges are the same width and are not offset from each other.
  • The criterion cell contains the expected text, number, or date value.
  • Values to be added are numeric; numbers stored as text may not behave like numeric amounts.
  • Duplicate headers are either intended to be included or separately distinguished.

For a quick audit, filter or visually mark the matching columns and add their values by hand for one sample row. If that check agrees with the formula, test a second row with a different pattern of values. This helps distinguish a formula-reference mistake from a data-quality problem.

Frequently Asked Questions

Can SUMIF work horizontally across columns?
Yes. Use the header row as the criteria range and the aligned values in the record’s row as the sum range. The ranges can be horizontal as long as they have matching dimensions and positions.

Will SUMIF add every column with the same header?
Yes. It includes the value from every cell whose corresponding header matches the criterion. If the repeated labels should be treated separately, add another identifying condition or use more specific headers.

Why does my formula return zero?
Check for mismatched text, extra spaces, incorrect cell references, or headers stored as dates when the criterion is text. Also check whether the cells in the sum range contain numbers rather than numeric-looking text.

Can I use a cell reference instead of typing the header in the formula?
Yes. Referencing a cell such as I1 makes the requested header easy to change and lets you copy the formula across a summary. Use suitable absolute and relative references so the ranges move as intended.

Should I use SUMIF or a lookup?
Use SUMIF when all matching columns should contribute to a total. Use a lookup when you want to retrieve a value from a matching column, usually where one match is expected. A lookup does not normally add duplicate matches together.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.