SUMIF in Excel: How It Works and How to Use It

Formulas & functions · Updated November 20, 2026

In Excel, SUMIF adds values that meet one condition: it checks a range against a criterion, then adds the corresponding values from a sum range. For example, it can total sales for one region, expenses above a threshold, or products whose names begin with particular letters.

What SUMIF does

SUMIF is a worksheet function for conditional addition. Rather than adding every number in a column, it tests each cell in a chosen range and includes a value in the result only when that cell satisfies the condition. The condition can test text, a number, a comparison such as “greater than,” or a text pattern.

A common use is summarizing a list. Imagine a worksheet with a region in column A and a sale amount in column B. SUMIF can look for “East” in column A and add the amounts from the matching rows in column B. The function returns one total; it does not filter or rearrange the underlying rows.

SUMIF is available in Microsoft Excel for Microsoft 365 on Windows, and the function works in Excel for Mac and Excel for the web as well. The formula syntax is the same across these versions. Excel’s regional settings may require semicolons instead of commas as argument separators; the examples here use commas.

Syntax and the three arguments

SUMIF takes a range to check, a criterion to apply, and an optional range of values to add. These are often called range, criteria, and sum_range. If you leave out sum_range, Excel adds the values in the range it checked. If you provide it, Excel checks the first range and adds the corresponding cells in the sum range.

That distinction matters. To find numbers above a threshold in a range and add those same numbers, you can omit sum_range. To find a region in one column and total amounts from another, include sum_range. Keep the two ranges the same size and shape, such as A2:A20 and B2:B20, so each checked row corresponds to the intended value.

The criterion can be typed directly into the formula or referenced from a worksheet cell. Text criteria need quotation marks when written directly in a formula. A criterion that compares numbers uses a comparison operator inside quotation marks, such as greater than or less than. When the operator comes from a cell, join it to the cell reference with the ampersand character.

A basic example with a sales list

Suppose A2:A6 contains the regions East, West, East, North, and West, while B2:B6 contains sales of 120, 90, 75, 110, and 60. To total the sales for East, use this formula:

With regions in A2:A6 and sales in B2:B6, the formula is =SUMIF(A2:A6,"East",B2:B6). It returns 195, because the two East rows contribute 120 and 75.

Notice that the criterion range is the region column, while the sum range is the sales column. Excel evaluates the region on each row and adds the sales amount on that same row when the region matches. The result is a number, so it can be formatted as currency or used in another calculation without changing the original data.

You can also refer to a cell containing the criterion. If D2 contains East, the formula becomes:

With regions in A2:A6, sales in B2:B6, and the requested region in D2, use =SUMIF(A2:A6,D2,B2:B6). It returns 195. This approach is handy when a report should update as someone chooses a different region in D2, rather than editing the formula each time.

Using numbers and comparison criteria

For numerical tests, put the comparison operator and number together in a quoted criterion. For example, a threshold can find values greater than 100, values less than 50, or values equal to 75. The available comparison operators include =, >, <, >=, and <=. In a worksheet formula, the operator is typed as a character; the HTML symbols here are displayed as their corresponding operators.

With sales amounts in B2:B6, this formula adds only amounts greater than 100: =SUMIF(B2:B6,">100"). It returns 230, from 120 and 110. Because the sum range is omitted, Excel tests and adds the same cells. The condition is strictly greater than 100, so a value of exactly 100 would not be included.

To make the threshold adjustable, put a number such as 100 in D2. With amounts in B2:B6 and the threshold in D2, use =SUMIF(B2:B6,">"&D2). It returns 230 when D2 contains 100. The ampersand joins the comparison operator to the cell’s value so Excel can evaluate the complete criterion.

Text criteria, partial matches, and wildcards

A text criterion can match a complete cell value, such as East, or use wildcards to match a pattern. The asterisk (*) represents any number of characters, including none. The question mark (?) represents exactly one character. Wildcards are useful when product names share a prefix or a code contains a variable character.

For example, suppose C2:C5 contains Tea bags, Teapot, Coffee, and Tea leaves, while D2:D5 contains 18, 25, 30, and 12. With product names in C2:C5 and values in D2:D5, this formula totals names beginning with Tea: =SUMIF(C2:C5,"Tea*",D2:D5). It returns 55, adding 18, 25, and 12. The match includes Teapot because the asterisk can stand for any characters after Tea.

Wildcards can also be escaped. Put a tilde (~) before an asterisk or question mark when you need to match that character literally, rather than use it as a wildcard. SUMIF’s text matching is not case-sensitive, so criteria for “east” and “East” treat those values as matches. If you need to distinguish capitalization, SUMIF alone is not a case-sensitive solution.

Working with dates

Excel stores real dates as serial numbers, so date conditions work best when cells contain actual Excel dates rather than date-looking text. A criterion can compare dates using the same operators used for numbers. For a fixed date, a formula can join the operator to a DATE function, which avoids ambiguity from typing a date in a regional format.

For example, to add amounts dated on or after January 1, 2026, assume A2:A10 contains real Excel dates and B2:B10 contains amounts. The formula =SUMIF(A2:A10,">="&DATE(2026,1,1),B2:B10) sums values from rows whose dates meet that boundary. The result depends on the dates and amounts in the worksheet.

To total a month, use two boundaries: on or after the first day of the month, and before the first day of the next month. This approach includes every time on the final day too, if the date cells also contain times. SUMIF supports only one condition, so use SUMIFS when both boundaries must be applied together.

With dates in A2:A4 (January 5, January 20, and February 3, 2026) and amounts in B2:B4 (40, 65, and 90), use =SUMIFS(B2:B4,A2:A4,">="&DATE(2026,1,1),A2:A4,"<"&DATE(2026,2,1)). It returns 105, adding the two January amounts and excluding the February entry.

SUMIF compared with SUMIFS

SUMIF handles one condition. SUMIFS handles multiple conditions and is the natural choice when a total depends on, for example, both a region and a minimum sale amount. The argument order is different: SUMIF starts with the condition range, while SUMIFS starts with the sum range, followed by each condition range and criterion. This difference is worth checking when adapting a formula.

With regions in A2:A6 and sales in B2:B6, use =SUMIFS(B2:B6,A2:A6,"East",B2:B6,">100") to total East sales above 100. It returns 120: only the East sale of 120 meets both conditions. If the task truly has just one condition, SUMIF is simpler; if it has two or more, SUMIFS makes the tests explicit.

Neither function changes the source data. If you need to display only matching rows, use Excel’s filter features rather than expecting a conditional-sum function to hide records. If you need a count instead of a total, COUNTIF or COUNTIFS is designed to count matching cells.

Common problems and how to fix them

  • The answer is zero: Check that the criterion exactly matches the data, that text criteria have the expected spelling and spaces, and that the sum range contains numbers rather than numbers stored as text. A criterion cell may also contain an unintended space.
  • The total is too high or too low: Confirm that the sum range lines up row by row with the criterion range. Check whether the condition should be greater than or greater than or equal to the threshold, and whether blank or duplicate rows are included.
  • A comparison is treated as text: Include an operator and value together in the criterion, such as a quoted greater-than condition. If the value is in a cell, join the operator and cell reference with an ampersand.
  • A date condition misses records: Confirm the date cells contain Excel dates, not text that merely looks like dates. Check the year and boundary operators; for a month total, use a start boundary and an exclusive next-month boundary with SUMIFS.
  • The formula shows an error after copying: Inspect the ranges and cell references. If the formula is copied, decide whether references should move or be fixed with dollar signs, such as $A$2:$A$20. Also check whether your Excel regional settings use semicolons as separators.

When debugging, temporarily inspect a few rows in the source data: does each criterion-range cell have the expected value, and is the paired sum-range cell numeric? This simple row-by-row check often reveals a shifted range, hidden space, or text-formatted number faster than rewriting the formula.

Practical habits for reliable totals

Make formulas easier to maintain by putting changeable criteria in clearly labeled cells, keeping source columns consistent, and using matching range boundaries. For lists that grow regularly, Excel Tables can help formulas and references adapt as rows are added. Use descriptive column headings, but keep headings out of the data ranges unless you intentionally want them tested.

For a report used by other people, label the result—for example, “East sales”—and make the criterion cell visible. Check the total against a small hand calculation or a filtered view when first setting up a summary. These checks are especially useful when a workbook combines imported data, because inconsistent spelling, spaces, and text-formatted values can make otherwise correct formulas appear wrong.

Frequently Asked Questions

Can SUMIF use more than one condition?
No. SUMIF applies one criterion. Use SUMIFS when the total must satisfy multiple conditions, such as a region and a date range.

Can SUMIF add cells in a different column from the one it checks?
Yes. Put the cells to test in the first range and the corresponding values to add in sum_range. Keep those ranges aligned so each test pairs with the correct value.

Is SUMIF case-sensitive?
No. Its text matching does not distinguish uppercase from lowercase. If capitalization must determine whether a match counts, use a different approach that explicitly performs case-sensitive comparisons.

What does SUMIF return when nothing matches?
It returns 0 when no cells meet the criterion. If the result is unexpectedly zero, check the criterion, data types, spaces, and the selected range.

Why use SUMIF instead of adding values manually?
SUMIF updates its total when the source values or criterion change, which is useful for recurring reports and larger lists. A manual total can become outdated or omit a matching row when the data changes.

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.