Excel SUMIF Formula Examples for Everyday Tasks

Excel’s SUMIF function adds values that meet one condition. With regions in A2:A7 and sales amounts in B2:B7, use =SUMIF(A2:A7,"North",B2:B7) to total North sales; if the North rows contain 120, 150, and 70, the result is 340.

What SUMIF does and how its arguments work

SUMIF checks a range for a condition, then adds values associated with the matching cells. It is useful for questions such as “How much did this department spend?” or “What is the total for products above a certain price?” The function uses up to three arguments, in this order: the range Excel checks, the criterion it tests, and the range it adds. The third argument is optional. If you leave it out, Excel adds the cells in the checked range itself.

The criteria range and sum range should usually have the same dimensions and line up row by row. For example, if A2 is a product category and B2 is its sale amount, those are a matching pair. SUMIF evaluates A2 against the condition and, if it matches, includes B2 in the total. It then repeats the process down the ranges. The returned result is one number, not a list of the matching rows.

A criterion can be text, a number, a comparison expressed as text, a wildcard pattern, or a reference to a cell that contains the condition. Text and comparison criteria are enclosed in straight double quotation marks when typed directly into a formula. The criteria cell reference itself is not quoted.

Basic SUMIF examples for text and numbers

Start with a small table: A2:A7 contains North, South, North, East, South, and North; B2:B7 contains 120, 80, 150, 90, 110, and 70. To add only the amounts whose region is North, with those regions in A2:A7 and amounts in B2:B7, enter =SUMIF(A2:A7,"North",B2:B7). The matching amounts are 120, 150, and 70, so the result is 340.

SUMIF can also test the values it will add. With amounts in B2:B7 as listed above, use =SUMIF(B2:B7,">=100") to add amounts that are at least 100. The qualifying values are 120, 150, and 110, so the result is 380. Since the same range is both checked and summed, the formula does not need a third argument.

For a criterion that changes, put the condition in a worksheet cell. With regions in A2:A7, amounts in B2:B7, and the word North in D2, enter =SUMIF(A2:A7,D2,B2:B7). It returns 340, and changing D2 to South recalculates the total as 190. This is convenient for a report where someone may select different categories without editing a formula.

Common criteria include exact text and numeric comparisons. In a typed criterion, put comparison operators inside the quotation marks:

With amounts in B2:B7, =SUMIF(B2:B7,">50") adds values greater than 50.
With amounts in B2:B7, =SUMIF(B2:B7,"<=100") adds values of 100 or less.
With status labels in A2:A20 and amounts in B2:B20, =SUMIF(A2:A20,"Paid",B2:B20) adds amounts on rows marked Paid.

These are separate examples: each assumes the ranges named in its description contain the relevant values. Remember that greater than is different from greater than or equal to, and that a comparison criterion must be written as one quoted text value.

Using wildcards for partial text matches

When labels are inconsistent or include extra words, wildcards can match part of a text value. Excel recognizes an asterisk (*) as any sequence of characters, including no characters, and a question mark (?) as exactly one character. A tilde (~) lets you search for a literal asterisk or question mark rather than treating it as a wildcard.

Suppose A2:A6 contains Desk lamp, Desk mat, Chair, Desk organizer, and Lamp, while B2:B6 contains 45, 15, 90, 30, and 25. With product names in A2:A6 and amounts in B2:B6, use =SUMIF(A2:A6,"Desk*",B2:B6) to add products whose names begin with Desk. The three matching amounts are 45, 15, and 30, giving 90.

With the same product table, =SUMIF(A2:A6,"*lamp*",B2:B6) matches text containing “lamp” anywhere in the name and returns 70: Desk lamp contributes 45 and Lamp contributes 25. For a one-character wildcard, consider product codes in A2:A20 and amounts in B2:B20; =SUMIF(A2:A20,"AB-?5",B2:B20) matches codes such as AB-X5 or AB-15, but not AB-XY5. Wildcards apply to text matching, so they are not a substitute for numeric comparisons.

SUMIF examples with dates

Excel stores dates as serial numbers, so a date criterion can be compared like a number. Use actual Excel date values in the date column, not text that only looks like a date. When a date is typed directly as a condition, its interpretation can depend on regional settings; building the criterion with DATE is a clear way to specify year, month, and day.

Suppose dates are in A2:A6 and amounts are in B2:B6. The dates are December 30, 2025; January 2, 2026; January 15, 2026; February 1, 2026; and February 3, 2026. The corresponding amounts are 50, 120, 80, 200, and 40. To add values from January 1, 2026 onward, use =SUMIF(A2:A6,">="&DATE(2026,1,1),B2:B6). It returns 440 because it includes 120, 80, 200, and 40.

To total one calendar month, use SUMIFS, which supports multiple conditions. With the same dates in A2:A6 and amounts in B2:B6, use =SUMIFS(B2:B6,A2:A6,">="&DATE(2026,1,1),A2:A6,"<"&DATE(2026,2,1)) for January 2026. The formula includes dates on or after January 1 and before February 1, so the result is 200. This half-open date interval also works when cells contain times, because it does not accidentally exclude dates with a time component late on the last day.

When to use SUMIFS instead

SUMIF handles one condition. If the total must satisfy two or more conditions at the same time, SUMIFS is generally the direct choice. Its argument order differs: the sum range comes first, followed by pairs of criteria ranges and criteria. A frequent mistake is to copy SUMIF’s argument order into SUMIFS.

For example, suppose A2:A5 contains North, North, South, North; B2:B5 contains Ana, Bo, Ana, Ana; and C2:C5 contains 120, 80, 150, and 90. With regions in A2:A5, representatives in B2:B5, and sales in C2:C5, use =SUMIFS(C2:C5,A2:A5,"North",B2:B5,"Ana") to total North sales assigned to Ana. The matching rows contain 120 and 90, so the result is 210.

For an “either this or that” condition, SUMIF by itself does not provide a dedicated OR argument. You can add separate SUMIF results when the categories cannot overlap. If one row might satisfy both conditions, adding the totals can count that row twice. Check the data and the intended logic before combining separate totals; a more carefully designed SUMIFS or another approach may be needed.

Common SUMIF problems and how to fix them

A formula that returns zero or an unexpected total often points to a mismatch between the data and the criterion. Check for leading or trailing spaces, inconsistent spelling, numbers stored as text, or dates stored as text. If a criterion refers to a cell, inspect that cell too: a stray space or an unexpected value can prevent a match. Excel’s TRIM function can help clean spaces in a separate helper column, but it does not automatically alter the source data used by SUMIF.

Check that the checked range and sum range cover the intended rows and have compatible dimensions. If the criteria range begins at row 2 but the sum range begins at row 3, the values can become misaligned. For a copied formula, use absolute references to keep the ranges fixed. With regions in A2:A100, amounts in B2:B100, and the criterion in D2, enter =SUMIF($A$2:$A$100,D2,$B$2:$B$100) before filling the formula down. The dollar signs lock the data ranges while D2 can change to D3, D4, and so on.

Also distinguish a genuinely blank cell from a cell containing a formula that returns an empty string. Blank-related criteria can behave differently from an explicit text value, and a wildcard criterion may match text in ways that are not obvious at first. If blanks matter to the calculation, test a few representative rows and compare the result with a filtered view or a small manual total.

If text should match exactly, verify spelling and spaces; SUMIF is not a spelling-correction tool.
If a numeric comparison is intended, confirm the source values are numbers rather than text strings.
If a date criterion fails, confirm the cells contain Excel dates and that the formula uses the correct date interval.
If the result is too large, check for overlapping OR conditions or a sum range that includes unintended rows.

Choosing a formula and keeping reports maintainable

Use SUM when every value in a range should be included, SUMIF when one condition selects the values, and SUMIFS when several conditions must all be true. For instance, a one-condition category total is easier to read as SUMIF than as a more elaborate formula. A report filtered by region, salesperson, and date range is a natural SUMIFS task.

For frequently updated lists, consider converting the data range into an Excel table with Insert > Table. Structured table references can make formulas easier to understand and can expand as rows are added. When building a small summary, keep criteria in clearly labeled cells and use absolute references when copying formulas. These habits reduce the chance that a later edit silently changes which rows are included.

SUMIF is available in current desktop Excel and Excel for the web. In Microsoft 365 for Windows, formulas use commas between arguments under standard regional settings; some regional configurations use semicolons instead. Excel for Mac has the same SUMIF behavior, though menu and keyboard details can differ from Windows. The formula examples here do not require a special add-in or a newer dynamic-array function.

Frequently Asked Questions

Can SUMIF add cells based on a condition in a different column?
Yes. Put the condition range first and the values to add in the third argument. For example, the North-sales example checks regions in column A and adds corresponding sales from column B.

Can SUMIF use more than one condition?
No. SUMIF is for a single condition. Use SUMIFS when every one of several conditions must be met, such as a particular region and a particular salesperson.

Why does my SUMIF formula return zero?
Common causes include a criterion that does not exactly match the source text, numbers or dates stored as text, extra spaces, or a range that points to the wrong cells. Check a matching row manually and confirm the criteria and sum ranges are aligned.

Can I use a cell reference as the SUMIF criterion?
Yes. Put the condition in a cell and use that cell reference as the criterion argument. If the condition includes an operator such as greater than, combine the operator in quotes with the cell reference using an ampersand.

Does SUMIF support wildcards?
Yes. Use an asterisk for any number of characters and a question mark for one character in a text criterion. Use a tilde before a wildcard character when you need to match that character literally.

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.