SUMIF in Excel: Examples and How to Use It

Excel’s SUMIF function adds values that meet one condition. For example, with product names in A2:A7 and sales amounts in C2:C7, =SUMIF(A2:A7,"Apples",C2:C7) returns 400 when the three Apples sales are 120, 150, and 130.

What SUMIF does

SUMIF is useful when you want a total for one category or rule: sales for a product, expenses above a threshold, or orders from a particular region. It checks a range against a criterion, then adds the corresponding values from a sum range. The criterion can be text, a number, a comparison, a wildcard pattern, or a reference to a cell containing the condition.

In Microsoft Excel for Microsoft 365 on Windows, the function is available directly in worksheet formulas. The same SUMIF formulas work in Excel for Mac and Excel for the web; ribbon locations or keyboard shortcuts are not needed to use the function. The examples below use comma separators, which are standard in many Excel installations. If your regional settings use semicolons as argument separators, Excel may require that separator instead.

Example data for the formulas

To make the examples easy to compare, use the following small sales table. Enter the headings in row 1 and the six records in rows 2 through 7:

Column A, Product: Apples, Bananas, Apples, Oranges, Bananas, Apples.
Column B, Region: East, West, West, East, East, East.
Column C, Sales: 120, 80, 150, 90, 110, 130.
Column D, Order date: January 5, January 12, February 3, February 18, March 2, March 15, all in 2026.

The figures are illustrative, and the dates need to be real Excel date values for date criteria to work as expected. Formatting a cell to look like a date does not always mean its contents are stored as a date; Excel dates are stored as serial numbers.

Basic SUMIF syntax and arguments

SUMIF has three arguments: the range to check, the criterion to apply, and the optional sum range. If you leave out the sum range, Excel adds the cells in the checked range itself. When you provide a sum range, Excel evaluates each position in the first range and adds the corresponding position in the sum range when the criterion matches.

For the Apples example, the assumed layout is product names in A2:A7 and sales amounts in C2:C7. The formula returns 400, from 120 + 150 + 130:

=SUMIF(A2:A7,"Apples",C2:C7)

The criteria range and sum range should normally cover corresponding rows and have the same shape. If one range starts on row 2 and the other starts on row 3, Excel can pair the wrong records or produce an unexpected total. Use clear, consistently sized ranges when building a summary.

Examples with text and cell references

A text criterion is written in quotation marks inside the formula. SUMIF text matching is not case-sensitive, so “apples” and “Apples” match the same entries. The criterion needs to match the cell contents; a trailing space in a source value can make it appear to match while actually failing to do so.

For a regional total, assume regions are in B2:B7 and sales are in C2:C7. This formula returns 450: the East records contribute 120 + 90 + 110 + 130.

=SUMIF(B2:B7,"East",C2:C7)

If the criterion may change, put it in a worksheet cell rather than editing the formula each time. Assume E2 contains the text Apples, with products in A2:A7 and sales in C2:C7. The formula returns 400, and changing E2 to Bananas changes the total to 190.

=SUMIF(A2:A7,E2,C2:C7)

This approach is handy for a report with a dropdown or a list of category names. The formula stays the same while the selected criterion changes. It also reduces mistakes caused by retyping a category differently in several formulas.

Examples with numbers and comparison criteria

For a numerical test, assume the sales values are in C2:C7 and you want to add only values greater than 100. The sum range can be omitted because the same cells are being tested and added. The result is 510, from 120 + 150 + 110 + 130.

=SUMIF(C2:C7,">100")

Comparison operators are written as part of the criterion, inside quotation marks. Excel supports greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), equal to (=), and not equal to (<>). For example, a criterion of "<>0" selects nonzero numeric values. A criterion like ">100" is different from the text value "100": the former applies a comparison, while the latter looks for a matching value.

When the comparison value is stored in a cell, join the operator to the cell reference. Assume F2 contains 100 and the sales amounts are in C2:C7. This returns 510, the total of amounts greater than the value in F2:

=SUMIF(C2:C7,">"&F2)

The ampersand combines the operator text with the value in F2 to make a criterion Excel can evaluate. This pattern is also useful for thresholds that users may edit.

Examples with wildcards and dates

Wildcards let a text criterion match a pattern rather than an entire value. An asterisk (*) represents any number of characters, while a question mark (?) represents one character. A tilde (~) before a wildcard asks Excel to treat that character literally, such as when a real asterisk appears in the source text.

Assume product names are in A2:A7 and sales are in C2:C7. The criterion below matches product names beginning with “App”; in the example table it returns 400 for the three Apples rows.

=SUMIF(A2:A7,"App*",C2:C7)

Dates can be criteria too, provided the cells contain valid Excel dates. To add sales for orders before March 1, 2026, assume dates are in D2:D7 and sales are in C2:C7. The result is 440: the January and February sales total 120 + 80 + 150 + 90.

=SUMIF(D2:D7,"<"&DATE(2026,3,1),C2:C7)

DATE creates a date value from year, month, and day, avoiding ambiguity about how a typed date is interpreted under different regional settings. For a rolling report, a date criterion can instead refer to a cell containing the cutoff date. If the dates are stored as text rather than actual date values, a date comparison may not behave as intended; convert or re-enter them as dates before relying on the total.

Using SUMIF for everyday summaries

SUMIF can turn a transaction list into a compact summary without manually filtering and adding values. Common applications include:

Adding sales by product, salesperson, department, or region.
Summing expenses that exceed a chosen amount or fall below a limit.
Adding entries with labels that begin with a shared prefix, using a wildcard.
Summing records before or after a single date boundary.
Creating a reusable report driven by a category or threshold cell.

For a summary table, place each category in its own row and use a criterion cell reference in the formula. You can then copy the formula down so each row calculates a different category. Check that the ranges remain appropriate when copied: if the data range should stay fixed, use absolute references such as $A$2:$A$7 and $C$2:$C$7. If the criterion cell should move down the summary, leave that reference relative.

SUMIF versus SUMIFS

SUMIF handles one condition. If the task requires two or more conditions, use SUMIFS, which adds values only when all specified conditions are met. For example, a request for sales of Apples in the East region has two conditions: product and region. SUMIFS is designed for that kind of test, while nesting separate SUMIF functions does not reliably express the same “both conditions” rule.

One important syntax difference is argument order. SUMIF puts the criteria range first and the optional sum range last. SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. Before switching functions, check the order carefully; formulas that look similar can total a different range if the arguments are rearranged incorrectly.

Use SUMIF when one rule is enough, such as all Apples or dates before a cutoff. Choose SUMIFS when the result must satisfy multiple rules at once, such as Apples in the East, or sales after one date and before another. For a simple one-condition total, SUMIFS can also be used, but SUMIF is often easier to read.

Common SUMIF problems and how to fix them

A total of zero or an unexpectedly small result often comes from a mismatch between the criterion and the source data. Check for leading or trailing spaces, inconsistent category spellings, and numbers stored as text. For numeric comparisons, confirm that the values in the checked range are numbers rather than text that merely looks numeric.

Wrong range: Confirm that the first range contains the field being tested and that the sum range contains the amounts to add.
Unequal range sizes: Make the criteria range and sum range line up row for row, typically using the same start and end rows.
Missing quotation marks: Put text criteria and comparison expressions such as ">100" in quotes. A cell reference used as the criterion does not need quotes.
Incorrect operator construction: When an operator is joined to a cell value, use the ampersand, as in the greater-than example above.
Dates treated as text: Confirm the date cells are true Excel dates and use DATE or a date-valued cell for the criterion.
Wildcard confusion: Use * for any number of characters and ? for exactly one character; use ~ to match a literal wildcard.

Also distinguish between a blank cell and a cell containing a space. A visually empty cell with a space is not truly blank, so a criterion intended to match empty entries may skip it. Cleaning imported data or using Find and Replace to remove unwanted spaces can resolve apparent inconsistencies.

Frequently Asked Questions

What is the difference between SUM and SUMIF?
SUM adds the numbers in the range or list you specify. SUMIF tests a range against one criterion and adds matching values, which makes it useful for category-based totals.

Can SUMIF use more than one condition?
No. SUMIF is for one condition. Use SUMIFS when multiple conditions must be true for a value to be included in the total.

Does SUMIF distinguish uppercase and lowercase text?
No. Its text matching is not case-sensitive. If case-sensitive matching is essential, a different formula approach is needed.

Why does my SUMIF formula return zero?
Check that the criteria matches the source values, the sum range is correct, and the ranges align. Also look for extra spaces, numbers stored as text, or dates stored as text.

Can I use SUMIF to add values for a date range?
SUMIF applies one criterion at a time. It can total dates before or after one cutoff; for a range with both a start and end date, SUMIFS is generally the more direct choice because it accepts multiple conditions.

What does the asterisk mean in a SUMIF criterion?
An asterisk is a wildcard for any number of characters. For example, “App*” matches text beginning with App. Use a tilde before an asterisk if you need to match an actual asterisk character in the data.

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.