Excel SUMIF Criteria Examples for Text, Dates, and Numbers

Excel’s SUMIF function adds values when one matching condition is true. With categories in A2:A7 and amounts in B2:B7, use =SUMIF(A2:A7,"Fruit",B2:B7) to add the amounts for rows labeled Fruit; if those amounts are 10, 5, and 2, the result is 17.

How SUMIF criteria work

SUMIF evaluates one range against one criterion, then adds the corresponding values from a sum range. Its three arguments are the range to check, the criterion to apply, and the values to add. The first two arguments are required; if you leave out the sum range, Excel adds the cells in the checked range that meet the condition.

The checked range and sum range should cover corresponding rows. For example, if the condition range is A2:A20, the sum range will usually be B2:B20. If the ranges are different sizes or start on different rows, Excel may return a result that does not match your intended row-by-row comparison.

SUMIF is available in Microsoft 365 for Windows, Mac, and the web, as well as many earlier Excel versions. The examples below use commas as argument separators, which is the standard in many installations. Some regional settings use semicolons instead; if Excel rejects a formula with commas, check your list-separator settings.

Exact text criteria

A text criterion in quotation marks matches that text in the condition range. Excel’s text matching with SUMIF is not case-sensitive, so Fruit and FRUIT are treated as the same criterion. Extra spaces are different, however: a cell containing Fruit followed by a space may not match the criterion Fruit.

With department names in A2:A20 and expenses in B2:B20, this adds expenses for the department named Support. If the matching rows contain 120, 80, and 50, the total is 250.

=SUMIF(A2:A20,"Support",B2:B20)

Text criteria can also refer to a cell rather than a fixed word. With department names in A2:A20, expenses in B2:B20, and the desired department entered in E2, use:

=SUMIF(A2:A20,E2,B2:B20)

This is useful for reusable summaries: change E2 to another department and the result updates. Do not put quotation marks around E2 in the formula, because that would make Excel look for the literal text E2 instead of reading the cell’s contents.

Numeric criteria and comparison operators

For an exact numeric match, you can use a number directly as the criterion. To apply a comparison, place an operator such as greater than, less than, or not equal to inside quotation marks along with the number. Operators supported in criteria include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

With unit prices in B2:B10 and quantities in C2:C10, the following adds quantities only for rows where the price is greater than 100. If the qualifying quantities are 2 and 4, the answer is 6.

=SUMIF(B2:B10,">100",C2:C10)

To make the threshold adjustable, put the threshold in E2. With prices in B2:B10, quantities in C2:C10, and the comparison value in E2, this adds quantities where the price exceeds the value in E2:

=SUMIF(B2:B10,">"&E2,C2:C10)

The ampersand joins the operator text to the value from E2. This is a common pattern for criteria based on a cell reference. It is also useful when a report needs to compare values against a target that changes regularly.

Wildcards for partial text matches

Use wildcards when a criterion should match text that contains a pattern rather than one exact entry. The asterisk (*) matches any number of characters, including none. The question mark (?) matches exactly one character. To search for either wildcard as a literal character, precede it with a tilde (~).

"North*" matches text beginning with North, such as North and North Region.
"*apple*" matches text containing apple anywhere, such as Green Apple Juice.
"A?" matches a two-character entry beginning with A, such as A7.
"~*" searches for a literal asterisk.

With product names in A2:A100 and sales in B2:B100, this adds sales for names containing the word apple, including entries such as Apple Juice and Green Apple:

=SUMIF(A2:A100,"*apple*",B2:B100)

Wildcards apply to text matching, not as a way to find numbers within numeric values. If product codes or identifiers contain numbers but are stored as text, they may behave as text criteria; check the data type and the exact cell contents when a pattern does not match.

Criteria for dates and date ranges

Excel stores real dates as serial numbers, even though cells display them in a date format. A criterion built from DATE is generally safer than typing a date string directly, because date strings can be interpreted differently under different regional settings. Use a comparison operator joined to a DATE value when you need dates on or after a boundary.

With transaction dates in A2:A100 and sales amounts in B2:B100, this adds sales dated January 1, 2026, or later. The result depends on the amounts in the matching rows.

=SUMIF(A2:A100,">="&DATE(2026,1,1),B2:B100)

SUMIF handles one condition, so a date interval with both a start and end boundary calls for SUMIFS. With transaction dates in A2:A100 and amounts in B2:B100, the following adds transactions from January 2026, including January 1 but excluding February 1:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))

Using an exclusive upper boundary is helpful when cells contain times as well as dates. A timestamp late on January 31 is still less than February 1, whereas a condition that stops at midnight at the start of January 31 could omit later transactions on that day.

Blank cells and nonblank cells

A blank criterion can total values on rows where the condition cell is empty. With status labels in A2:A20 and invoice amounts in B2:B20, this adds amounts for rows with blank status cells:

=SUMIF(A2:A20,"",B2:B20)

To add amounts where the status cell is not blank, use the not-equal operator with an empty string. With the same layout of statuses in A2:A20 and amounts in B2:B20, use:

=SUMIF(A2:A20,"<>",B2:B20)

A formula that returns an empty text string is not always the same as a genuinely empty cell for every purpose in Excel. If blank-related results look unexpected, inspect whether the cells are truly empty or contain formulas, spaces, or other invisible characters.

When to use SUMIFS instead

SUMIF is designed for one criterion. If the total should depend on two or more conditions, use SUMIFS, which places the sum range first and then takes pairs of criteria ranges and criteria. For instance, a report might total sales for one region and one product category at the same time.

With regions in A2:A100, categories in B2:B100, and sales in C2:C100, this totals sales where the region is West and the category is Hardware. If those matching sales are 300 and 125, the result is 425.

=SUMIFS(C2:C100,A2:A100,"West",B2:B100,"Hardware")

Remember the argument-order difference: SUMIF begins with the criteria range, while SUMIFS begins with the sum range. The names are similar, but copying arguments from one function into the other without changing their order can produce errors or misleading totals. SUMIFS also supports criteria on the same range more than once, as in the date-interval example above.

Common SUMIF problems and how to fix them

The total is zero: Check that the criterion matches the source values, including spaces and punctuation. For number or date comparisons, verify that the source cells contain actual numbers or dates rather than text that merely looks like them.
The formula misses some text rows: Look for leading or trailing spaces, inconsistent labels, or hidden characters. Wildcards can help with intentional partial matches, but they should not conceal inconsistent source data in a report that needs exact categories.
A comparison criterion is written incorrectly: Put an operator and a fixed value together in a quoted criterion, or join the operator to a cell reference with an ampersand. A cell reference used for the threshold should not be enclosed in quotation marks.
The total is unexpectedly high or low: Confirm that the criteria range and sum range cover the same rows and that the sum range contains the values you intend to add. Check whether numbers are stored as text, which may not behave like numeric amounts.
A date formula behaves differently on another computer: Prefer DATE(year,month,day) to an ambiguous typed date string, and confirm that the source cells are real Excel dates. A text date may not compare as expected.
You need more than one condition: Switch to SUMIFS rather than trying to put several separate conditions into SUMIF. SUMIF does not provide an AND-style list of criteria.

SUMIF does not treat capitalization as a distinct condition. If the task genuinely requires case-sensitive summing, SUMIF alone is not sufficient; a different formula approach is needed. Also, SUMIF totals matching values regardless of whether their rows are hidden by a filter. If the requirement is to sum only visible filtered rows, choose a method designed for visible cells instead of assuming SUMIF will follow the filter.

Frequently Asked Questions

Can SUMIF use a cell reference as its criteria?
Yes. Put the criteria value in a cell and refer to that cell as the criterion. When the criterion includes a comparison operator, join the operator to the reference with an ampersand so Excel can evaluate the combined condition.

How do I sum values that contain a word?
Use an asterisk on both sides of the word in a text criterion. For example, a pattern such as "*apple*" matches text with apple anywhere in the cell. The matching remains case-insensitive.

Can SUMIF add values for a date range?
Not by itself when the range needs both a start and end condition. Use SUMIFS with the date range specified twice: once for the lower boundary and once for the upper boundary. DATE is a reliable way to construct the boundary values.

Does SUMIF distinguish uppercase from lowercase?
No. SUMIF text criteria are not case-sensitive, so capitalization alone does not separate matching entries. Use another formula approach if capitalization must determine which rows qualify.

Why does SUMIF return zero even though I can see matching values?
Check for extra spaces, text-formatted numbers or dates, a criterion that does not match the data exactly, and criteria or sum ranges that do not line up. If using an operator, confirm it is joined correctly to the numeric threshold or cell reference.

Choosing the right example for your worksheet

Start by identifying the cells Excel should inspect, the condition those cells must meet, and the values to add from matching rows. Use an exact text or number criterion for a single category, an operator for a threshold, wildcards for partial text, and DATE-based criteria for calendar boundaries. Use SUMIFS as soon as the total depends on multiple conditions.

Once the formula works, test it against a small set of rows whose expected total you can calculate manually. This simple check helps catch range misalignment, values stored as text, and criteria that are broader or narrower than intended. Keeping source labels consistent and placing adjustable criteria in cells also makes a SUMIF summary easier to maintain.

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.