SUMIF in Excel: Meaning, Syntax, and How to Use It

Formulas & functions · Updated November 18, 2026

In Excel, SUMIF adds numbers only when their matching cells meet one condition; it is a conditional sum. Its basic form is =SUMIF(range,criteria,sum_range) in concept, but the arguments in a usable formula must be replaced with cell references or values. The equals sign at the start tells Excel to calculate the formula.

What SUMIF means in Excel

SUMIF is the name of an Excel function: “SUM” describes adding numbers, while “IF” describes the condition that decides which numbers to add. For example, it can total sales for one region, expenses in one category, or quantities for a particular item. Instead of filtering a table and adding the visible values yourself, SUMIF evaluates the condition across a range and returns a total.

SUMIF does not mean that a particular value is mathematically equal to another value, and it is not an alternative spelling for the equals operator. The function checks whether cells meet a criterion, then sums corresponding values. The criterion can be an exact match, such as the text East, or a test such as greater than 100.

A SUMIF formula begins with =, as Excel formulas generally do. If you type a function name without that equals sign, Excel may treat the entry as text rather than calculate it. SUMIF is available in current Microsoft 365 Excel on Windows, as well as many earlier Excel versions.

How the arguments work

SUMIF has three arguments in this order:

  • Range: The cells Excel checks against the condition.
  • Criteria: The condition to apply, such as East or a numeric comparison.
  • Sum_range: The cells to add when the corresponding cells in range meet the condition. This argument is optional.

The matching relationship is positional: Excel checks the first cell in range and, if it qualifies, adds the corresponding first cell in sum_range. It then moves through the ranges in order. The criteria range and sum range should therefore line up row by row or column by column. If you omit sum_range, Excel adds the cells in range that meet the criterion. That is useful when the values being tested are also the values to total.

For example, with region names in A2:A6 and sales amounts in B2:B6, A2:A6 is the range, East is the criterion, and B2:B6 is the sum_range. The formula returns one number: the total sales associated with rows whose region is East.

A basic SUMIF example

Suppose A2:A6 contains East, West, East, East, West, while B2:B6 contains 120, 80, 45, 60, 95. With region names in A2:A6 and those sales amounts in B2:B6, use:

=SUMIF(A2:A6,"East",B2:B6)

The result is 225, because the East rows contribute 120 + 45 + 60. The West amounts are ignored. This example shows why the range and sum_range are different: the first contains labels to test, while the second contains numbers to add.

You can put the criterion in a cell instead of writing it into the formula. With the same regions in A2:A6, sales in B2:B6, and East entered in D2, use:

=SUMIF(A2:A6,D2,B2:B6)

The result is also 225 while D2 contains East. A cell reference is useful when a report reader should be able to change the selected region without editing the formula. If D2 is changed to West, the result becomes 175, the sum of 80 and 95.

Criteria for text, numbers, and comparisons

A criterion can be a text value, a number, a comparison, or a pattern using wildcards. For a text match, place the text in quotation marks in the formula, as in the East example. SUMIF text matching is not case-sensitive, so East and EAST match the same text for this purpose. Extra spaces are different characters, however: a cell containing East followed by a space may not match East exactly.

Comparison criteria use operators such as greater than, less than, greater than or equal to, less than or equal to, and not equal to. Because the operator and value form a criterion, put them together inside quotation marks. With sales figures in B2:B6, the following formula adds amounts strictly greater than 100:

=SUMIF(B2:B6,">100")

The result is 120. The sum_range is omitted, so Excel tests and adds the same cells. The value 100 itself would not qualify because the operator is greater than, not greater than or equal to.

To use a comparison value stored in a cell, join the operator to the reference with the ampersand. This is useful for changing a cutoff without rewriting the formula. Check that the cutoff cell contains a numeric value, not text that merely looks like a number, if the comparison is meant to be numeric.

Wildcards and date criteria

For text criteria, an asterisk (*) matches any sequence of characters, and a question mark (?) matches one character. For example, Tea* can match Tea, Teabag, or Teapot. With product names in A2:A5 containing Tea, Teamug, Coffee, and Teabag, and their amounts in B2:B5 containing 5, 12, 8, and 7, use:

=SUMIF(A2:A5,"Tea*",B2:B5)

The result is 24, adding 5, 12, and 7. To search for an actual asterisk or question mark in cell text rather than use it as a wildcard, precede that character with a tilde (~). This distinction matters when product codes or labels include punctuation.

Dates are stored by Excel as serial numbers, even when the cells display a calendar date. For criteria based on a date boundary, use a real date value and combine it with the comparison operator. With dates in A2:A5 (December 30, 2025; January 1, 2026; January 15, 2026; February 1, 2026) and amounts in B2:B5 (40, 65, 90, 30), use:

=SUMIF(A2:A5,">="&DATE(2026,1,1),B2:B5)

The result is 185, the total for January 1, 2026, and later. DATE constructs a date value, avoiding ambiguity from typing a date in a regional display format. If the range includes times as well as dates, remember that a date with a time later that day is greater than the date value at midnight.

SUMIF versus SUMIFS

Use SUMIF when one condition is enough. If a total must meet two or more conditions, use SUMIFS, which supports multiple criteria pairs. A common example is totaling sales for East only when the sale is also at least 50. With regions in A2:A6 and sales in B2:B6 as in the earlier example, use:

=SUMIFS(B2:B6,A2:A6,"East",B2:B6,">=50")

The result is 180: the East sales of 120 and 60 qualify, while 45 is below 50. Notice the argument order: SUMIFS starts with the sum range, followed by a criteria range and criterion for each condition. SUMIF starts with the criteria range and criterion, then takes the optional sum range. Mixing up these orders is a frequent source of confusing results.

For exactly one condition, SUMIF is usually simpler to read. For a rule such as “region is East and product is Tea,” SUMIFS is the natural choice. If the logic is more complex—for example, one condition or another across separate groups—consider whether helper columns or another approach will make the rule clearer and easier to maintain.

Common SUMIF problems and how to fix them

If a result is zero or unexpectedly small, first check whether the criterion truly matches the cells. Look for leading or trailing spaces, inconsistent labels, numbers stored as text, and dates that are actually text. A criterion that looks right on screen can still differ from the underlying cell contents.

  • Ranges do not align: Make sure the criteria range and sum range cover corresponding rows. When the ranges are different sizes or shifted, the result may not reflect the intended records.
  • Criteria punctuation is wrong: Put comparison operators and their values together in the criteria, and use quotation marks for text criteria. For a condition built from a cell reference, join the operator and reference with &.
  • The wrong values are being added: Confirm that sum_range points to the numeric values, not the labels being checked. If sum_range is omitted, SUMIF sums the criteria range itself.
  • A total seems duplicated: SUMIF adds every qualifying row. If the source data contains duplicate records, the function counts their amounts too; it does not identify or remove duplicates.
  • Dates fail to match: Confirm the cells are real Excel dates and account for any times in the values. A date stored as text will not behave like a date serial in a comparison.

For a quick check, inspect a few qualifying rows manually and add their amounts. Also select the formula cell to review its references, then verify that the criterion and both ranges cover the intended records. These checks are often faster than changing a formula at random.

Related functions and practical choices

SUMIF is for adding values that meet one condition. COUNTIF counts cells that meet one condition but does not total a separate range of amounts. SUMIFS adds values that meet multiple conditions. These functions sound similar, but choosing the one that matches the question keeps a workbook easier to interpret.

For a one-time total, filtering a table and using a subtotal may be sufficient. For a recurring report, a SUMIF formula updates as source values change and makes the condition visible in the worksheet. If users need totals across several categories, a PivotTable may be easier to explore interactively. The right option depends on whether the priority is a fixed calculation, a reusable formula, or a flexible summary.

When the source is an Excel Table, structured references can make formulas more readable and easier to extend as rows are added. Whatever layout you use, keep category values consistent and avoid mixing labels, dates, and amounts in the same column. Clean input data usually matters more than a complicated formula.

Frequently Asked Questions

What does SUMIF equal in Excel?
SUMIF is a function that returns the sum of values meeting one condition. It does not equal one fixed number; the result depends on the worksheet data and the criterion.

What is the equals sign in a SUMIF formula for?
The opening equals sign tells Excel to interpret the entry as a formula. The condition itself can use an equals comparison, but for an exact text match you can usually provide the text as the SUMIF criterion.

Can SUMIF use more than one condition?
SUMIF handles one condition. Use SUMIFS when a total must satisfy multiple criteria, such as a particular region and a minimum sales amount.

Why does my SUMIF return zero?
Check for a mismatch between the criterion and source cells, including extra spaces, text-versus-number differences, text-formatted dates, or a criteria range that points to the wrong column. Also make sure the sum range contains the values you intend to add.

Does SUMIF match uppercase and lowercase differently?
No. SUMIF is not case-sensitive for ordinary text criteria. It does distinguish text that differs in other ways, such as extra spaces or different characters.

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.