SUMIF in Excel: Practical Examples and Formulas

In Excel for Microsoft 365, SUMIF adds values that meet one condition. Its syntax is =SUMIF(range, criteria, [sum_range]); for example, with product names in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"Coffee",B2:B6) totals sales for Coffee. The examples below show how to use SUMIF with text, numbers, dates, wildcards, and criteria stored in cells.

What SUMIF does and how its arguments work

SUMIF checks each cell in a criteria range, tests whether it matches a condition, and adds the corresponding value from a sum range. It is useful for questions such as “How much did this product sell?”, “What is the total for amounts above a threshold?” or “How much revenue came from a particular region?”

The function has three arguments, with the third optional:

range is the cells Excel checks against your condition.
criteria is the condition, such as a word, a number, or a comparison like greater than 100.
sum_range contains the numbers to add when the matching cells in range meet the condition. If you omit it, Excel adds the matching cells in range itself.

When you provide both range and sum_range, keep them the same size and shape. Excel pairs their cells by position: the first cell in the criteria range corresponds to the first cell in the sum range, the second to the second, and so on. SUMIF is designed for one condition; for conditions that must all be true, use SUMIFS instead.

Example: add sales for one text category

Suppose A2:A6 contains Coffee, Tea, Coffee, Juice, and Tea, while B2:B6 contains 12, 8, 15, 10, and 5. To find Coffee sales, check the product names and add the corresponding sales amounts. With those cells as the layout, use =SUMIF(A2:A6,"Coffee",B2:B6). The result is 27, because 12 plus 15 equals 27.

Text criteria are not case-sensitive: “coffee” and “Coffee” match the same entries. You can also keep the condition in a worksheet cell so it is easy to change. If D2 contains Coffee and the product names and sales remain in A2:A6 and B2:B6, use =SUMIF(A2:A6,D2,B2:B6). The result is again 27. A cell reference is especially helpful in a report where readers choose a product from a list or enter a different category to see its total.

Example: sum numbers above or below a threshold

For numeric comparisons, put the comparison operator and the number together inside quotation marks. For example, if B2:B5 contains 120, 85, 240, and 160, the formula =SUMIF(B2:B5,">100") adds the values greater than 100. The result is 520 (120 + 240 + 160). Because the sum range is omitted, Excel checks and adds the same cells.

If the cutoff is stored in a cell, join the operator to the cell reference with an ampersand. With the same values in B2:B5 and a cutoff of 100 in D2, use =SUMIF(B2:B5,">"&D2). This also returns 520. The ampersand combines the operator with the value in D2 to form the criterion “greater than 100.” Other common operators are < for less than, >= for greater than or equal to, <= for less than or equal to, and <> for not equal to.

Example: match text with wildcards

Wildcards let a text criterion match a pattern rather than an exact word. The asterisk (*) matches any number of characters, including none; the question mark (?) matches exactly one character. Suppose A2:A5 contains North Region, Northeast, South Region, and North Coast, and B2:B5 contains 40, 25, 30, and 15. With those ranges, =SUMIF(A2:A5,"*North*",B2:B5) returns 80, adding 40, 25, and 15 because each corresponding label contains North.

A pattern such as AB? would match three-character entries beginning with AB, such as AB1 or ABC, but not AB12. If you need to match a literal asterisk or question mark in the cell text rather than use it as a wildcard, precede the character with a tilde. For example, the criterion "~*" looks for a literal asterisk. Wildcards are for text matching; they do not turn a partial numeric value into a numeric comparison.

Example: sum values for dates

Excel stores real dates as serial numbers, so SUMIF can compare them with date criteria. Assume A2:A5 contains actual Excel dates Jan. 3, Jan. 15, Dec. 30, and Feb. 2, 2026, and B2:B5 contains 12, 20, 5, and 9. To add values on or after Jan. 1, 2026, use =SUMIF(A2:A5,">="&DATE(2026,1,1),B2:B5). The result is 41, since all four dates are on or after that date and the amounts total 12 + 20 + 5 + 9.

For a year or month total, one boundary alone may include later dates than you intend. A robust way to set both a start and an end boundary is SUMIFS, which supports multiple conditions. For example, a date range from Jan. 1 up to, but not including, Jan. 1 of the following year can be expressed with a lower-bound and upper-bound condition. Make sure the source cells contain real dates, not date-looking text; text may fail to compare as expected. Also use DATE or a cell containing a real date rather than typing a date in a format that could be interpreted differently under another regional setting.

Example: omit the sum range when adding the checked cells

You do not always need a separate sum range. If A2:A5 contains Billable, Nonbillable, Billable, and Billable, and those cells themselves contain numeric values 2, 3, 4, and 1 instead of labels, a one-range SUMIF can add the matching values. More commonly, use this form when the cells being tested are also the values you want to sum: with numbers 2, 3, 4, and 1 in A2:A5, =SUMIF(A2:A5,">2") returns 4, because only the value 3 and 4 qualify and their sum is 7—so the result is actually 7.

That last example illustrates an important check: for “greater than 2,” the qualifying numbers are 3 and 4, and 3 + 4 = 7. When the criterion range contains labels and the values to add are elsewhere, include sum_range; otherwise Excel will try to add the matching cells in the criteria range itself. SUMIF generally ignores text in cells it is asked to sum, but that does not make a text column a substitute for the numeric sum range you intended.

Use SUMIFS when there is more than one condition

A common SUMIF question is how to total values that meet two requirements, such as a region and a month. SUMIF cannot directly test both conditions at once. Use SUMIFS, whose first argument is the sum range, followed by pairs of criteria ranges and criteria.

Suppose A2:A5 contains East, East, West, and East; B2:B5 contains Jan, Feb, Jan, and Jan; and C2:C5 contains 100, 150, 80, and 75. To total sales where the region is East and the month is Jan, use =SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Jan"). The result is 175 (100 + 75). In contrast, SUMIF is a good fit when only one condition is needed. Choosing the function that matches the question makes formulas easier to read and reduces the risk of leaving out a condition.

Common SUMIF problems and how to fix them

If a formula returns zero or a number that seems wrong, check the criteria and the source data before changing the formula. These are frequent causes:

Range sizes do not line up. Keep the criteria range and sum range aligned and the same shape, for example A2:A100 with B2:B100. Avoid including a header in one range but not the other.
Numbers are stored as text. A cell can look numeric but be text imported from another system. Convert the source values to numbers before relying on numeric sums or comparisons.
Dates are text rather than dates. Check whether the cells are recognized as dates by Excel. Formatting text to look like a date does not necessarily convert its underlying value.
Spaces or inconsistent labels prevent an exact match. “East” and “East ” can behave as different text. Clean imported labels or use a carefully chosen wildcard criterion if partial matching is appropriate.
The comparison operator is not inside the criterion. Use ">100" for a fixed threshold or ">"&D2 for a threshold in D2. A bare text value such as 100 does not mean “greater than 100.”
The formula points to the wrong cells when copied. If you plan to fill a formula down, consider absolute references such as $A$2:$A$100 so the source ranges stay fixed while other references change as intended.

For repeatable reports, use an Excel Table where practical. Structured references can expand as rows are added, helping avoid a formula that stops before newly appended records. Whatever range style you use, verify a sample by manually adding a few matching values and checking that the total agrees.

Frequently Asked Questions

Can SUMIF use a condition from another cell?
Yes. Put the condition in a cell and use that cell as the criteria argument, such as =SUMIF(A2:A20,D2,B2:B20). For a comparison, join the operator and reference, as in =SUMIF(A2:A20,">"&D2,B2:B20).

Is SUMIF case-sensitive?
No. SUMIF does not distinguish uppercase and lowercase letters in its text criteria. If case-sensitive matching is essential, a different formula approach is needed.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and places the criteria range first. SUMIFS handles multiple conditions and places the sum range first. That argument order difference is a common source of formula errors.

Why does SUMIF return zero even though I see matching entries?
Check for extra spaces, mismatched text, criteria written as a comparison without quotation marks, or a sum range containing text instead of numeric values. For dates, confirm that the cells are real Excel dates rather than text that only looks like a date.

Can SUMIF add values from another worksheet?
Yes. Use a sheet reference in the ranges. With regions in Orders!A2:A100 and amounts in Orders!C2:C100, =SUMIF(Orders!A2:A100,"East",Orders!C2:C100) sums the East amounts. If a sheet name contains spaces, enclose the name in single quotation marks, for example 'January Orders'!A2:A100.

Does SUMIF work with Excel Tables?
Yes. SUMIF can use table columns as its criteria and sum ranges. Table references can make formulas easier to understand and generally adjust as the table grows; ensure that both referenced columns correspond to the intended records.

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.