SUMIF Excel Criteria: Syntax, Examples, and Common Fixes

Formulas & functions · Updated November 18, 2026

In Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with regions in A2:A20 and sales in B2:B20, =SUMIF(A2:A20,"East",B2:B20) adds the sales for rows whose region is East.

What SUMIF criteria do

SUMIF is a conditional-sum function: it tests one range against one criterion, then adds corresponding numbers from a sum range. The range is where Excel looks for matches; the criterion describes what counts as a match; and the optional sum range contains the values to add. If you leave out the sum range, Excel adds the matching cells in the range itself.

That distinction matters in a typical worksheet. A department column might contain text labels, while a separate amount column contains the figures to total. SUMIF checks each department label and adds the amount on the same row when the label meets the criterion. It returns one total, not a list of matching rows.

In Microsoft Excel for Microsoft 365 on Windows, enter the function in a blank cell and press Enter. Excel for Mac and Excel for the web use the same SUMIF function and formula structure. The examples below use commas as argument separators; some regional settings require semicolons when entering formulas, even though the function and argument order are unchanged.

How the function is organized

SUMIF takes a range to evaluate, a criterion to apply, and, when needed, a sum range. The first two arguments are required; the third is optional. Keep the criteria range and sum range aligned row by row and the same size, so it is clear which amount belongs to each tested cell.

With product names in A2:A20 and quantities in B2:B20, the following totals the quantities where the product is Apples:

=SUMIF(A2:A20,"Apples",B2:B20)

When the criterion range is also the range to add, the third argument can be omitted. With quantities in A2:A20, this adds only quantities greater than 10:

=SUMIF(A2:A20,">10")

Excel evaluates the criterion for each cell in the first range. If a cell qualifies, it adds the corresponding cell in the sum range. This is why the order matters: the criterion range comes first, followed by the criterion, then the optional sum range.

Exact text and numeric criteria

For a straightforward text match, put the text in quotation marks inside the formula. With categories in A2:A15 and costs in B2:B15, this adds costs categorized as Travel:

=SUMIF(A2:A15,"Travel",B2:B15)

Text criteria are not case-sensitive in SUMIF. A criterion of “travel” matches entries such as Travel or TRAVEL. If capitalization must distinguish matches, SUMIF alone is not sufficient; a case-sensitive solution needs a different formula approach.

A number can be used directly as a criterion. With unit prices in A2:A15 and order values in B2:B15, this adds values for rows where the unit price is exactly 25:

=SUMIF(A2:A15,25,B2:B15)

For exact text matching, ordinary characters are treated literally unless they are wildcard characters. Inconsistent spaces, spelling, or punctuation can therefore prevent an apparent match. For example, “East ” with a trailing space may not match “East”. If results seem unexpectedly low, inspect the source entries rather than assuming SUMIF is ignoring rows.

Comparison operators and cell references

To test for greater than, less than, equal to, or not equal to, place the operator and comparison value together in a text criterion. The supported comparison operators include >, <, >=, <=, and <>. With scores in A2:A30 and payments in B2:B30, this sums payments for scores of at least 80:

=SUMIF(A2:A30,">=80",B2:B30)

When the threshold is stored in a cell, join the operator text to the cell reference with the ampersand. With scores in A2:A30, payments in B2:B30, and the threshold in D1, use:

=SUMIF(A2:A30,">="&D1,B2:B30)

That construction lets you change D1 without editing the formula. A common mistake is writing the reference inside quotation marks, which makes Excel treat it as text instead of reading the cell’s value. The operator itself must remain in quotes, while the reference stays outside them.

For a rule that combines a comparison with a text label, place the full test in the criteria argument. With dates in A2:A30 and amounts in B2:B30, this adds amounts for dates on or after the date in D1:

=SUMIF(A2:A30,">="&D1,B2:B30)

If a criterion cell is blank, a comparison may not express the intended rule. Check the contents and data type of the referenced cell, especially when users can edit it.

Wildcards for partial text matches

SUMIF supports wildcards in text criteria. An asterisk (*) matches any number of characters, including none; a question mark (?) matches exactly one character. Use them when values contain additional text around the word or when one character can vary.

  • *east* matches text containing east anywhere, such as Northeast or East Office.
  • East* matches text beginning with East.
  • *-A matches text ending in -A.
  • AB? matches three-character entries starting with AB, such as AB1 or ABX.

With descriptions in A2:A25 and amounts in B2:B25, this sums amounts for descriptions containing the word fee:

=SUMIF(A2:A25,"*fee*",B2:B25)

Wildcards do not make the match case-sensitive; SUMIF remains case-insensitive. If you need to match a literal asterisk or question mark instead of using it as a wildcard, precede that character with a tilde (~). For example, the tilde is Excel’s escape character for wildcard criteria. Be careful with broad patterns such as *East*: they can match more entries than a reader expects, so review the text values and test the pattern on a small sample.

Using SUMIF with dates

Excel stores real dates as serial values, so SUMIF can compare dates using the same greater-than and less-than operators used for numbers. Build the criterion from an operator and a date value. Using DATE(year,month,day) avoids ambiguity between regional date formats. With transaction dates in A2:A100 and amounts in B2:B100, this totals entries from January 2026:

=SUMIF(A2:A100,">="&DATE(2026,1,1),B2:B100)-SUMIF(A2:A100,">="&DATE(2026,2,1),B2:B100)

The first SUMIF includes dates from January 1 onward; subtracting the total from February 1 onward leaves the January dates. For this example, the calculation assumes the date cells contain real Excel dates and that each transaction falls within the intended range. If the data includes times, the less-than-first-of-next-month approach also includes all times on January 31. With dates in A2:A100 and amounts in B2:B100, that version is:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))

SUMIFS is used here because it can apply both date boundaries in one function. Dates that look correct but are stored as text may not compare as expected. Check that the cells are actual dates, not text strings formatted to look like dates.

SUMIF versus SUMIFS

SUMIF is designed for one criterion. Use it when one condition is enough, such as adding all sales for a region. SUMIFS is the related function for two or more conditions, such as a region and a date period. Its argument order differs: SUMIFS places the sum range first, followed by criteria-range and criterion pairs.

With regions in A2:A100, dates in B2:B100, and sales in C2:C100, this sums sales for East during January 2026:

=SUMIFS(C2:C100,A2:A100,"East",B2:B100,">="&DATE(2026,1,1),B2:B100,"<"&DATE(2026,2,1))

SUMIFS applies its conditions together: a row must meet every specified criterion to contribute to the total. This is an AND relationship, not an OR relationship. For example, to total either East or West, separate SUMIF calculations can be added; with regions in A2:A100 and sales in B2:B100, use:

=SUMIF(A2:A100,"East",B2:B100)+SUMIF(A2:A100,"West",B2:B100)

Choose the simplest function that expresses the rule. SUMIF is easier to read for one condition, while SUMIFS avoids cumbersome combinations when several conditions must all be true.

Common problems and how to check them

A formula can be syntactically valid and still return zero or an unexpected total. Check these points before rebuilding it:

  • Range alignment: Confirm that the criteria and sum ranges cover corresponding rows. A shifted range may add the wrong values.
  • Text consistency: Look for extra spaces, spelling differences, or punctuation differences. A wildcard can help with partial text, but may also match unintended entries.
  • Numbers stored as text: Numeric-looking values imported as text can behave differently from actual numbers. Check the source data and convert it if needed.
  • Criteria punctuation: For a comparison, include the operator in the criterion, such as “>=10”. When referencing a threshold cell, join the operator and reference rather than enclosing both as text.
  • Dates: Confirm the cells contain Excel date values. A display format does not convert text into a true date.
  • Formula separators: If Excel reports a formula error, your regional settings may expect semicolons instead of commas between arguments.

Also check whether the sum range contains numbers. Text in the sum range is not added as a numeric amount. If the worksheet is large, test the criterion against a few known rows and calculate their expected total manually; that helps identify whether the issue is the rule, the source data, or the selected ranges.

Frequently Asked Questions

Does SUMIF criteria support multiple conditions?
SUMIF accepts one criterion. For multiple conditions that must all be true, use SUMIFS. For an OR condition, add separate SUMIF results or choose another formula structure that represents the alternatives.

Are SUMIF text criteria case-sensitive?
No. SUMIF text matching is case-insensitive, so uppercase and lowercase versions of the same text match. A case-sensitive total requires a different formula approach.

How do I sum cells that contain a word?
Use asterisks around the word in a text criterion. For example, *fee* matches entries containing fee anywhere. With descriptions in A2:A25 and amounts in B2:B25, =SUMIF(A2:A25,"*fee*",B2:B25) adds amounts for those matches.

Can SUMIF criteria refer to another cell?
Yes. Put a reference in the criterion argument. For a comparison such as greater than or equal to a threshold, combine the quoted operator with the reference using &, as shown in the comparison examples above.

Why does SUMIF return zero when matching text is visible?
Check for extra spaces, hidden characters, differences in spelling, and a shifted range. Also verify that the matching rows’ sum-range cells contain numeric values. A wildcard may help when the criterion should match only part of each text entry.

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.