The SUMIF function adds values that meet one condition, such as totaling sales for one product or expenses above a chosen amount.
These SUMIF formula examples show how to build criteria for text, numbers, dates, and partial matches in Excel and Google Sheets.
SUMIF syntax and argument order
SUMIF uses a criteria range to check a condition and a sum range to identify the values to add:
=SUMIF(range, criteria, [sum_range])
rangeis the set of cells Excel or Sheets checks against the condition.criteriais the condition, which can be text, a number, an expression, or a cell reference.sum_rangeis optional and contains the values to add.
If omitted, the function sums matching cells inrange.
For example, if product names are in A2:A6 and sales amounts are in B2:B6, =SUMIF(A2:A6,"Apples",B2:B6) adds the sales amounts on rows where the product is Apples.
The criteria range and sum range should have matching dimensions so each checked row corresponds to the value being added.
SUMIF examples for exact text and cell references
Suppose a worksheet contains this small sales table:
A2:A6: Apples, Pears, Apples, Oranges, PearsB2:B6: 12, 8, 15, 10, 7
To total sales for Apples, use =SUMIF(A2:A6,"Apples",B2:B6).
The result is 27, because the two matching amounts are 12 and 15.
If the product name is entered in D2, refer to that cell instead: =SUMIF(A2:A6,D2,B2:B6).
This makes the summary reusable; changing D2 to Pears returns 15.
Exact text matching in SUMIF is not case-sensitive, so apples and Apples match the same entries.
SUMIF examples with greater-than and less-than criteria
Comparison criteria combine an operator with a value.
Put the operator and fixed number inside quotation marks.
If amounts are in B2:B6, =SUMIF(B2:B6,">10") adds the values greater than 10 in that range.
It returns 27 for the sample amounts 12, 8, 15, 10, and 7.
Common operators include > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), and <> (not equal to).
To sum values in B2:B6 that are not 10, use =SUMIF(B2:B6,"<>10").
When the threshold is stored in D2, join the operator to the cell reference with an ampersand: =SUMIF(B2:B6,">"&D2).
If D2 contains 10, this sums values greater than 10.
A common mistake is writing ">D2", which looks for criteria text containing the characters D2 instead of using the cell's value.
SUMIF examples with wildcards
Wildcards help when text entries contain extra words or when only part of a label is known.
In Excel and Google Sheets, the asterisk (*) matches any number of characters, while the question mark (?) matches one character.
=SUMIF(A2:A6,"App*",B2:B6)sums amounts for labels beginning with “App,” such as Apples.=SUMIF(A2:A6,"*ples",B2:B6)matches labels ending in “ples.”=SUMIF(A2:A6,"P?ars",B2:B6)matches a five-character label such as Pears, with any single character in place of?.
To match a literal asterisk or question mark in cell text, precede the wildcard with a tilde, as in "~*".
This is useful when the data includes symbols as actual characters rather than wildcard patterns.
SUMIF examples for dates
Spreadsheet dates are stored as serial values, so criteria can use date functions instead of relying on a manually typed date string.
If dates are in A2:A6 and amounts are in B2:B6, this formula sums amounts dated on or after January 1, 2026:
=SUMIF(A2:A6,">="&DATE(2026,1,1),B2:B6)
For a date stored in D2, use =SUMIF(A2:A6,">="&D2,B2:B6).
Ensure the cells contain real spreadsheet dates, not text that merely looks like a date.
If the date range includes timestamps and you need all entries on one day, a single SUMIF comparison may not capture the full day; SUMIFS with a start-date criterion and an exclusive next-day criterion is a better fit.
When to use SUMIFS instead
SUMIF handles one condition.
To sum sales for Apples in a particular region, use SUMIFS, which supports multiple criteria ranges:
=SUMIFS(C2:C100,A2:A100,"Apples",B2:B100,"East")
This adds values in C2:C100 only when the corresponding product in column A is Apples and the region in column B is East.
In SUMIFS, the sum range comes first, unlike SUMIF, where the criteria range comes first.
Keeping that difference in mind prevents formulas from summing the wrong column.
Common SUMIF problems and fixes
- Unexpected zero: Check spelling, leading or trailing spaces, and whether numbers or dates are stored as text.
The criteria must match the underlying cell values. - Wrong range alignment: Use criteria and sum ranges that cover corresponding rows.
Avoid ranges with different starting rows or sizes. - Numbers stored as text: A cell that displays
12may contain text rather than a numeric value.
Convert the data to numbers before relying on numeric comparisons or totals. - Missing quotation marks: Text criteria need quotes, as in
=SUMIF(A2:A10,"Apples",B2:B10).
Criteria that use a comparison operator and fixed value also need quotes. - Wrong result after copying: Use absolute references such as
$A$2:$A$100when a range should stay fixed as you copy a formula down. - Unexpected blanks: The criterion
""targets blank cells, while"<>"targets nonblank cells.
Results can differ when formulas return empty text.
Compatibility in Excel and Google Sheets
SUMIF is available in modern desktop and web versions of Microsoft Excel, including Excel for Microsoft 365, Excel 2024, Excel 2021, and Excel 2019, as well as Google Sheets.
The function name and core argument order are consistent across both applications.
Regional settings may require semicolons instead of commas between arguments, and date formats can vary by locale; use the spreadsheet's native date values when building date criteria.