SUMIF Excel Multiple Columns: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To use SUMIF with multiple columns, add separate SUMIF results for each value column, then combine them with SUM; SUMIF does not accept several independent criteria columns in one call. If you need to test more than one condition, use SUMIFS, and if you need both multiple conditions and multiple value columns, sum separate SUMIFS calculations or create a row-total helper column.

What SUMIF Can and Cannot Do

SUMIF adds values that meet a condition. Its syntax is SUMIF(range, criteria, [sum_range]): range is where Excel checks the condition, criteria is the condition to match, and sum_range is the corresponding cells to add. The sum_range argument is optional; if omitted, Excel adds the cells in range that meet the condition.

The key limitation behind the question is that SUMIF takes one criteria range and one criterion. Its sum_range is intended to correspond cell-for-cell with that criteria range, not to act as a collection of unrelated columns that Excel should total independently. For a criterion in one column and amounts in several other columns, write one SUMIF for each amount column. For conditions in multiple columns, use SUMIFS instead.

“Multiple columns” can mean several different layouts, so identify what you need to test and what you need to add before choosing a formula:

  • One condition, several amount columns: add one SUMIF per amount column.
  • Several conditions, one amount column: use SUMIFS.
  • Several conditions and several amount columns: use multiple SUMIFS calculations or total each row first with a helper column.
  • Criteria and values arranged horizontally: SUMIF can work across a matching row of cells.

Sum Several Value Columns for One Match

Suppose A2:A6 contains salesperson names, while B2:D6 contains three product sales columns. The rows are Ana: 120, 40, 10; Ben: 80, 70, 0; Ana: 50, 30, 20; Cara: 60, 25, 15; and Ana: 30, 20, 5. Put the name to find in F2. To add all three product columns for Ana, use one SUMIF for each column and add their results.

With names in A2:A6, product amounts in B2:D6, and the salesperson to match in F2, enter:

=SUM(SUMIF($A$2:$A$6,$F2,$B$2:$B$6),SUMIF($A$2:$A$6,$F2,$C$2:$C$6),SUMIF($A$2:$A$6,$F2,$D$2:$D$6))

The result is 325. Ana’s values total 200 in column B, 90 in column C, and 35 in column D; 200 + 90 + 35 = 325. The dollar signs lock the source ranges so the formula can be copied to other criteria cells without the source data shifting. F2 remains flexible by row, so copying the formula down can use F3, F4, and so on.

For a report that shows each product subtotal separately, use a SUMIF per product column rather than combining the results. With the same data layout and Ana in F2, the Product B subtotal formula is:

=SUMIF($A$2:$A$6,$F2,$B$2:$B$6)

It returns 200. Replace the sum_range with C2:C6 or D2:D6 to get 90 or 35. Keeping the subtotals separate makes it easier to compare product categories and spot which column contributes to the overall result.

Use SUMIFS for Conditions in Multiple Columns

When a row must meet two or more conditions, SUMIFS is usually the direct choice. Unlike SUMIF, SUMIFS starts with the amount range to add, followed by pairs of criteria ranges and criteria. Conditions supplied as separate pairs are combined as AND: the row must satisfy every condition to be included.

For example, assume A2:A6 contains salesperson names, B2:B6 contains regions, and C2:C6 contains sales. The records are Ana, East, 120; Ana, West, 80; Ben, East, 90; Ana, East, 50; and Ben, West, 70. Put Ana in F2 and East in G2.

With names in A2:A6, regions in B2:B6, sales in C2:C6, and the desired name and region in F2 and G2, enter:

=SUMIFS($C$2:$C$6,$A$2:$A$6,$F2,$B$2:$B$6,$G2)

The result is 170: it adds the two Ana records in East, 120 and 50, while excluding Ana’s West sale and both Ben records. All the ranges in SUMIFS should have matching dimensions, so the criteria rows line up with the corresponding sales rows.

If you mean either East or West rather than both conditions at once, treat that as an OR requirement. One clear approach is to add two SUMIFS calculations. With Ana in F2, East in G2, and West in H2, use:

=SUM(SUMIFS($C$2:$C$6,$A$2:$A$6,$F2,$B$2:$B$6,$G2),SUMIFS($C$2:$C$6,$A$2:$A$6,$F2,$B$2:$B$6,$H2))

The result is 250, comprising Ana’s East total of 170 plus her West total of 80. This works cleanly because a record cannot be both East and West at the same time. More generally, be careful when adding results for OR conditions: if a record can match more than one of the criteria, it could be counted more than once.

Add a Helper Column for Repeated Reports

If you regularly need the total across several value columns for each row, a helper column can make the workbook simpler. In the earlier salesperson example, use E2:E6 for row totals. With each row’s values in B:D, enter this in E2 and fill it down through E6:

=SUM(B2:D2)

For the first row, the result is 170 because 120 + 40 + 10 = 170. The remaining row totals are 150, 100, 100, and 55. Once the helper column contains the total for each record, a single SUMIF can add those totals by salesperson.

With names in A2:A6, row totals in E2:E6, and the requested salesperson in F2, use:

=SUMIF($A$2:$A$6,$F2,$E$2:$E$6)

The result for Ana is 325. This method is often easier to audit than a long formula containing a separate SUMIF for every value column. It is especially useful when a report has many amount columns or when users need to see each row’s combined total. The trade-off is that the helper column adds data to the worksheet and must be included when new rows are added.

When the Data Runs Across Columns

SUMIF is not limited to vertical lists. If the condition labels and values are both arranged horizontally, use matching horizontal ranges. For instance, suppose B1:D1 contains Jan, Feb, Jan, B2:D2 contains 100, 200, 50, and F1 contains Jan. Here, the criteria range and sum range are both one row wide and have the same three-column shape.

With month labels in B1:D1, amounts in B2:D2, and the month to match in F1, enter:

=SUMIF($B$1:$D$1,$F$1,$B$2:$D$2)

The result is 150, adding the two values labeled Jan: 100 + 50. This is a useful distinction: SUMIF can inspect multiple cells in a criteria range and add corresponding values, but that is not the same as asking it to apply one vertical condition range to several separate amount columns. In the horizontal example, the labels and amounts line up cell-for-cell across the same row.

Choose the Right Formula for the Job

Before building a formula, translate the request into a simple rule: which cells contain the condition, how many conditions must be met, and which numbers should be added? That avoids trying to make SUMIF handle a layout it was not designed for. These patterns cover most common cases:

  • One condition, one amount column: use SUMIF.
  • Several conditions, one amount column: use SUMIFS, with one criteria-range-and-criteria pair per condition.
  • One condition, several amount columns: add separate SUMIF calculations, or use a helper column containing each row’s combined amount.
  • Several conditions, several amount columns: add separate SUMIFS calculations for the value columns, or create a helper total and use SUMIFS against it.

For a small, fixed number of value columns, separate calculations are transparent and easy to verify. If the number of columns changes often, a helper column can reduce maintenance. If the data can be reshaped so each record has one amount field and a separate category field, ordinary SUMIF or SUMIFS reports may become simpler. The best choice depends on how the source data is used, not just on making a formula as short as possible.

Common Errors and How to Fix Them

A formula can return a plausible but incorrect total if the ranges do not line up. Check these issues when the result is zero, too large, or inconsistent with a manual total:

  • Different-sized ranges: make sure each criteria range covers the same records as its associated sum range. In SUMIFS, the sum range and criteria ranges should have matching dimensions.
  • Wrong column used as the criteria range: confirm the formula checks the field that actually contains the condition, such as the salesperson column rather than the product amount column.
  • Number stored as text: a cell that looks like a number may be stored as text and may not add as expected. Check the source data and convert values to numeric data where appropriate.
  • Extra spaces or inconsistent labels: Ana and Ana followed by a space are not identical text. Standardize imported names or categories if matching results are unexpectedly missing.
  • Relative references shift when copying: lock ranges with dollar signs, as in $A$2:$A$6, while leaving a criteria cell such as F2 relative when each copied row should use a different criterion.
  • Overlapping OR criteria: adding separate conditional totals can count a record more than once if it matches multiple conditions. Design the criteria to be mutually exclusive or use a method that counts each qualifying row once.

For a quick check, calculate the subtotals for the separate value columns, then add those displayed numbers and compare them with the combined result. Testing a criterion that has a small, easily verified number of matching rows is also useful. If the formula is copied across a report, inspect the first and last copied formulas to confirm that the locked ranges stayed fixed and the intended criteria references changed.

Formula Compatibility and Regional Settings

SUMIF and SUMIFS are established Excel worksheet functions and are available in Microsoft 365 for Windows. The examples use standard comma separators and English-language function names. Depending on a computer’s regional settings, Excel may require semicolons instead of commas when formulas are entered. If Excel flags a correctly structured formula as invalid, check the list separator used by that installation rather than changing the function’s logic.

The formulas above do not rely on newer dynamic-array functions, so they are suitable for ordinary shared workbooks as well as current Microsoft 365 workbooks. Excel for Mac has the same SUMIF and SUMIFS worksheet functions; the formulas do not need a different Mac version. Excel for the web also supports these functions. The main practical difference across platforms is usually the regional formula separator or the way a workbook is opened and edited, not the calculation pattern.

Frequently Asked Questions

Can SUMIF sum two columns at once?

For a vertical criteria range and two separate value columns, use one SUMIF per value column and add the results with SUM. Alternatively, calculate a row total in a helper column and use one SUMIF on that helper column. Keep the criteria and sum ranges aligned by row.

Can SUMIF check two criteria columns?

No. SUMIF accepts one criteria range and one criterion. Use SUMIFS when a row must meet conditions in two or more columns. SUMIFS combines its separate condition pairs as AND, meaning every stated condition must be satisfied.

What is the difference between SUMIF and SUMIFS?

SUMIF is designed for one condition. SUMIFS supports multiple conditions and uses a different argument order: its sum range comes first, followed by criteria-range and criterion pairs. Both functions add values from a specified range when their conditions are met.

Can I use SUMIF when my data is arranged horizontally?

Yes. Use a horizontal criteria range and a horizontal sum range with the same number of cells, so each label corresponds to its value. The formula can then add the values under labels that meet the criterion.

Why does SUMIF return zero even though the name appears in the list?

Check that the criterion text matches the source, including spaces, and that the criteria range and sum range cover corresponding rows. Also check whether the values to add are genuine numbers rather than text. A hidden trailing space in an imported name is a common reason an apparent match is missed.

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.