SUMIF Function Examples: Text, Numbers, Dates in Excel

Excel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6, use =SUMIF(A2:A6,"Apples",B2:B6) to total sales for Apples; if the rows contain sales of 120, 80, 45, 60, and 30 for Apples, Oranges, Apples, Pears, and Apples, the result is 195.

What SUMIF Does

SUMIF is useful when you want a total for one category or one rule: sales for a particular product, expenses above a threshold, or quantities recorded on a particular date. It checks each cell in a criteria range, then adds the corresponding value from a sum range when the check is true.

The function has three arguments: the range Excel checks, the condition it applies, and the cells it adds. The sum range is optional. If you leave it out, Excel adds qualifying numbers from the criteria range itself. SUMIF evaluates one criterion; it does not add several separate conditions together in the same function call.

In Microsoft 365 for Windows, enter a formula in a cell and press Enter. The function is also available in Excel for Mac and Excel for the web, though ribbon placement and some interactions can differ. The examples here use comma argument separators, the standard syntax for the Excel installations described.

Basic Syntax and Matching Rows

Keep the criteria range and sum range aligned: each row in one should correspond to the same row in the other. In the opening example, Excel checks A2 through A6 and adds the value from the matching row in B2 through B6. The three Apples rows contribute 120 + 45 + 30, for a total of 195.

Text matching in SUMIF is not case-sensitive. “Apples,” “APPLES,” and “apples” are treated as the same criterion. For an exact text value, type it inside straight double quotation marks in the formula. If the criterion is in a worksheet cell, refer to that cell instead, as shown later.

You can omit the sum range when the values to test are also the values to add. With numbers in B2:B6—120, 80, 45, 60, and 130—this formula adds only the numbers greater than 100: =SUMIF(B2:B6,">100"). The result is 250. In most category summaries, however, the criteria and values to total live in separate columns, so specify both ranges.

Examples for Numbers and Thresholds

Numeric criteria can test whether a value is greater than, less than, equal to, or different from a number. Put comparison operators inside the criterion text. If you need a comparison with a value stored in another cell, join the operator to the cell reference with the ampersand operator.

With quantities in B2:B6 and revenue in C2:C6, where quantities are 4, 12, 10, 7, and 15 and revenue is 20, 60, 50, 35, and 75, the formula =SUMIF(B2:B6,">=10",C2:C6) totals revenue for quantities of at least 10. The result is 185: 60 + 50 + 75.
With quantities in B2:B6 and revenue in C2:C6, and a cutoff value of 10 in D2, use =SUMIF(B2:B6,">"&D2,C2:C6) to total revenue for quantities strictly greater than the cutoff. The result is 135, from the rows with quantities 12 and 15.
With the same quantity and revenue layout, use =SUMIF(B2:B6,"<10",C2:C6) to include values below 10. The result is 55, from quantities 4 and 7.

Notice the difference between “greater than 10” and “greater than or equal to 10.” The first excludes 10; the second includes it. That distinction often explains a total that looks unexpectedly high or low.

Text Criteria, Wildcards, and Blank Cells

For category totals, a text criterion can identify a complete label. With categories in A2:A5—N-101, N-205, S-100, and N-330—and amounts in B2:B5—25, 40, 30, and 55—=SUMIF(A2:A5,"N-205",B2:B5) returns 40.

Wildcards let you match a text pattern instead of a single complete value. An asterisk represents any number of characters, while a question mark represents one character. A tilde before a wildcard treats it as a literal character rather than a pattern symbol.

With those same codes and amounts, =SUMIF(A2:A5,"N-*",B2:B5) adds codes that begin with N-. It returns 120 (25 + 40 + 55).
With product names in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,"?ear",B2:B5) matches a four-character name ending in “ear,” such as “pear.” The result depends on the matching rows in the worksheet.

Blank criteria are useful when an empty category cell has meaning. With categories in A2:A5—Apples, blank, Oranges, blank—and values in B2:B5—10, 15, 20, and 25—=SUMIF(A2:A5,"",B2:B5) returns 40, the total beside the two blank cells. A criterion of "<>" matches nonblank cells; in that same layout, =SUMIF(A2:A5,"<>",B2:B5) returns 30. If cells appear blank but contain spaces or formulas returning an empty string, check the underlying cell contents because they may not behave like genuinely empty cells in every context.

Using a Cell as the Criterion

Referencing a cell makes a summary easier to reuse. A user can change the selection in the criterion cell without editing the formula. With product names in A2:A6, sales in B2:B6, and the word Apples in D2, =SUMIF(A2:A6,D2,B2:B6) returns 195. Change D2 to Oranges and the same formula returns 80.

For a comparison based on a cell, combine the operator and reference. With quantities in B2:B6, revenue in C2:C6, and 10 in D2, =SUMIF(B2:B6,">="&D2,C2:C6) returns 185 for the illustrative quantity and revenue values above. Do not put the operator and the reference together inside quotation marks: Excel needs to evaluate the cell reference as a reference, not as literal text.

Adding Values for a Date Range

Dates are stored as serial values in Excel, so SUMIF can match a single date or a date criterion. For a month or other period, though, you usually need a lower and upper boundary. SUMIF only accepts one condition, so SUMIFS is the better fit when the same rows must satisfy both boundaries.

With dates in A2:A5—January 5, January 15, February 2, and January 31, 2026—and amounts in B2:B5—25, 40, 30, and 55—use =SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1)). The result is 120, adding the three January amounts. Using a less-than February 1 boundary includes every time on January 31 if the date cells contain timestamps; a less-than-or-equal boundary at midnight on January 31 could miss later times that day.

For a single date, SUMIF can be enough. With actual Excel dates in A2:A5 and amounts in B2:B5, =SUMIF(A2:A5,DATE(2026,1,5),B2:B5) returns 25 for the sample data. Using DATE avoids ambiguity between regional date formats. If a formula returns zero unexpectedly, check that the cells contain real Excel dates rather than date-looking text.

SUMIF or SUMIFS?

Choose SUMIF when one condition is sufficient. Choose SUMIFS when several conditions must all be true for a row to contribute, such as a product being Apples and a region being West, or a date falling between two boundaries. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria; that argument order differs from SUMIF.

With products in A2:A5, regions in B2:B5, and sales in C2:C5, where the rows are Apples/West/120, Apples/East/45, Oranges/West/80, and Apples/West/30, =SUMIFS(C2:C5,A2:A5,"Apples",B2:B5,"West") returns 150. Both conditions must match. SUMIF does not provide a direct way to require those two different conditions at once; nesting or adding formulas can be possible in special cases, but SUMIFS is clearer for an AND-style summary.

One subtle difference matters when ranges are different sizes: SUMIF can adjust a misaligned sum range in ways that make the result difficult to spot, while mismatched dimensions can also produce errors or misleading results in related formulas. The safest practice is to use criteria and sum ranges with the same starting row and number of rows.

Common SUMIF Problems to Check

Wrong range order: SUMIF takes the criteria range first and sum range third. Reversing them can make Excel test amounts instead of categories.
Numbers stored as text: A cell that looks numeric may be text, so it may not compare or add as expected. Check for inconsistent imports, leading apostrophes, or spaces.
Criteria punctuation: Comparisons such as greater than 10 need the operator included in the quoted criterion. When the number comes from a cell, concatenate the operator and reference.
Hidden spaces in labels: “West” and “West ” can be different text values. Cleaning imported data or using a carefully chosen wildcard can help, but a wildcard may also match unintended labels.
Range sizes or starting rows differ: Keep the criteria and sum ranges aligned so every condition points to the intended amount.
Dates are text or include times: Confirm dates are numeric Excel dates, and use a boundary that includes all timestamps in the intended period.

When a total seems wrong, test a small range first and inspect which rows meet the criterion. Also verify that the criterion refers to the intended cell and that the sum range contains the values you want added. SUMIF silently returning a plausible number can be harder to notice than a visible formula error.

Frequently Asked Questions

Can SUMIF use more than one condition?
Not in one SUMIF call. Use SUMIFS when every condition must be true, such as matching both a product and a region. For a requirement where either of two conditions can qualify, separate SUMIF calculations may be appropriate, but take care not to count the same row twice.

Does SUMIF distinguish uppercase and lowercase?
No. Standard SUMIF text criteria are case-insensitive, so capitalization differences do not create separate categories. If case-sensitive matching is essential, SUMIF alone is not the right tool.

Can SUMIF add values from another worksheet?
Yes. A range reference can point to another sheet in the same workbook. For example, a formula can use a sheet-qualified criteria range and sum range; the sheet name must be quoted when it contains spaces or punctuation. Confirm that both ranges refer to the intended rows.

Why does SUMIF return zero?
Common causes include a criterion that does not exactly match the data, numbers or dates stored as text, extra spaces, or a sum range that points to empty or incorrect cells. Compare the criterion with the underlying values and confirm the range alignment.

Can SUMIF criteria use wildcards?
Yes. Use an asterisk for any number of characters and a question mark for one character in a text criterion. Use a tilde to escape a wildcard when you need to match the asterisk or question mark itself.

Is SUMIF available in Google Sheets too?
Yes, Google Sheets also has SUMIF, with a similar purpose and familiar argument pattern. This guide describes Excel, so check the application’s own help if you are using Sheets and need details about its interface or behavior.

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.