SUMIF Formula Criteria Examples for Excel: Rules and Tips

Use Excel’s SUMIF function to add values that match one condition, such as a product name, a minimum amount, or a date threshold. With product names in A2:A7 and sales amounts in B2:B7, for example, the total for Pen is =SUMIF(A2:A7,"Pen",B2:B7), which returns 30 when the three Pen sales are 12, 8, and 10.

How SUMIF criteria work

SUMIF checks each cell in a criteria range against one condition. When a cell meets that condition, Excel adds the corresponding value from the sum range. The function has three arguments: the range to check, the criterion to apply, and—optionally—the range of values to add. If you leave out the sum range, Excel adds the matching numbers in the criteria range itself.

For the examples below, imagine a worksheet with product names in A2:A7 and sales amounts in B2:B7:

Pen — 12
Notebook — 25
Pen — 8
Folder — 15
Pen — 10
Notebook — 20

The rows need to line up: 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 can use ranges on the same sheet or on another sheet in the same workbook. Keep the criteria range and sum range the same size and shape to make the matching relationship clear and avoid unexpected results.

Exact text and cell-reference criteria

For an exact text match, place the text in quotation marks inside the formula. Excel’s text matching with SUMIF is not case-sensitive, so Pen and PEN are treated alike. The formula below uses the product and sales layout above and returns 30:

With product names in A2:A7 and sales amounts in B2:B7, use:

=SUMIF(A2:A7,"Pen",B2:B7)

You can put the criterion in a worksheet cell instead of typing it into the formula. This makes a small summary report easier to update: change the selected product in D2, and the result recalculates. With product names in A2:A7, sales amounts in B2:B7, and the chosen product Pen in D2, this returns 30:

=SUMIF(A2:A7,D2,B2:B7)

A cell reference is especially useful when several summary rows use different criteria. It also reduces the chance of a spelling mismatch between the criterion and the data. If a criterion cell contains extra spaces, however, it may not match the source text as expected; clean the source or criterion values before assuming the function is wrong.

Number criteria and comparison operators

Criteria can test numbers using greater than, less than, equal to, or not equal to. Put the comparison operator and a fixed number together inside quotation marks. If the criteria range is also the range to sum, the third argument can be omitted. With sales amounts in B2:B7, the following formula adds amounts greater than 15 and returns 45 (25 plus 20):

=SUMIF(B2:B7,">15")

For a changing threshold, join the operator to a cell reference with the ampersand character. With sales amounts in B2:B7 and the threshold 15 in D2, this formula also returns 45:

=SUMIF(B2:B7,">"&D2)

Common operators are 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 ">=15" includes 15, while ">15" excludes it. When the criterion is a number stored as text, Excel may not compare it as you intend, so check that the source values are genuinely numeric.

Wildcards for partial text matches

Wildcards let a text criterion match a pattern rather than one complete entry. An asterisk represents any number of characters, including none; a question mark represents exactly one character. With product names in A2:A7 and sales in B2:B7, the formula below matches names beginning with Pen and returns 30:

=SUMIF(A2:A7,"Pen*",B2:B7)

To find a string anywhere within a cell, put an asterisk at both ends of the search text. With the same layout, this formula matches Notebook and returns 45:

=SUMIF(A2:A7,"*book*",B2:B7)

A question mark can stand for one character, which is helpful when the rest of a label is consistent. If the text you need to match contains a literal asterisk or question mark, prefix that character with a tilde (~) so Excel treats it as text rather than as a wildcard. Wildcards apply to text matching, not to numeric thresholds.

Blank cells, nonblank cells, and dates

An empty-string criterion can find blank cells in the criteria range; a not-equal-to-empty criterion can find nonblank cells. These are useful for totals based on whether a category or status has been entered. For example, with categories in A2:A7 and amounts in B2:B7, the formula below adds amounts on rows where the category is blank:

=SUMIF(A2:A7,"",B2:B7)

For dates, use a real Excel date value as the criterion rather than relying on how a date is displayed on screen. A robust way to express a fixed boundary is with the DATE function. With transaction dates in A2:A5 and amounts in B2:B5—December 20, 2025 (10), January 3, 2026 (40), January 18, 2026 (60), and February 1, 2026 (25)—the formula returns 125, the sum for dates on or after January 1, 2026:

=SUMIF(A2:A5,">="&DATE(2026,1,1),B2:B5)

Excel stores dates as serial numbers, even when cells are formatted to display calendar dates. If a date criterion seems to miss entries, check whether the source cells contain actual dates rather than text that only looks like dates. For a period with both a start and end date, such as a month, SUMIF alone is not enough because it accepts one condition; use SUMIFS instead.

When to use SUMIFS instead

SUMIF handles one criterion. Use SUMIFS when you need two or more conditions, such as totaling sales for Pen in the West region, or transactions between two dates. SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. That argument order differs from SUMIF, so it is worth checking before adapting a formula.

For a two-condition example, assume products are in A2:A7, regions in C2:C7, and sales in B2:B7. If you want Pen sales from West, make sure the data includes the region for each row; the following formula adds only rows where both conditions match:

=SUMIFS(B2:B7,A2:A7,"Pen",C2:C7,"West")

The example returns the sum of the sales amounts on rows labelled Pen and West; its exact total depends on the values in those rows. For a date window, use one date condition for the start and another for the end, both against the date range. Choose SUMIF for a single test and SUMIFS for multiple tests; adding helper columns just to combine conditions is often unnecessary.

Common SUMIF problems and fixes

If a correct-looking formula returns zero or a result that seems too small, compare the actual cell contents with the criterion. Common causes include spelling differences, leading or trailing spaces, numbers stored as text, dates stored as text, and an incorrect range reference. Also check whether the sum range begins and ends on the same rows as the criteria range.

Text does not match: Check spelling and spaces. The criterion Pen does not match Pencil as an exact match, although a wildcard can match it.
Threshold gives the wrong total: Check whether the operator includes the boundary. A greater-than test excludes the threshold; a greater-than-or-equal-to test includes it.
Formula changes when copied: Relative references move when you fill a formula. If a range must stay fixed, select the reference and press F4 in Excel for Windows to cycle through reference types. On Mac, the shortcut can vary by keyboard settings; use the equivalent reference-toggle shortcut supported by your Excel setup.
Result is unexpectedly large: Confirm that the sum range contains the values you intend to add and that your criteria are not matching more rows than expected.

When troubleshooting, test the criterion against a few individual rows and verify the source values in the formula bar. Filtering the data to show matching entries can also help you compare the intended matches with the rows included in the total. Avoid changing the formula repeatedly before checking whether the underlying data has inconsistent types or hidden spaces.

Choosing and maintaining criteria

For a one-off calculation, a typed criterion is quick and readable. For a report that other people will update, a criterion cell is usually easier to maintain. Put labels such as product, region, or threshold beside the result so it is clear what the total represents. When several totals share a pattern, use the same layout for each criterion and copy the formula carefully.

For recurring workbooks, Excel Tables can make data easier to extend because new rows are included when the table grows. You can also keep data and summary criteria in clearly labelled sections rather than embedding values in long formulas. Whether you use ordinary ranges or a table, test the result against a small sample you can add by hand. A calculated total is easier to trust when the matching rows and arithmetic are straightforward to verify.

Frequently Asked Questions

Does SUMIF criteria support more than one condition?
SUMIF supports one criterion. Use SUMIFS when you need multiple conditions, such as a product and region together or both a start date and an end date.

How do I use greater than with SUMIF?
Put the operator and a fixed threshold in quotation marks, such as the criterion ">15". To use a threshold stored in a cell, join the operator to that cell with an ampersand.

Can SUMIF criteria use wildcards?
Yes. Use an asterisk for any number of characters and a question mark for one character. Use a tilde before a wildcard character when you need to match that character literally.

Is SUMIF case-sensitive?
No. SUMIF’s text criteria are not case-sensitive, so differences in capitalization alone do not create separate matches. Use another method if your calculation specifically requires case-sensitive matching.

Why does my SUMIF formula return zero?
Check that the criterion matches the source values, that the ranges align, and that numbers or dates are stored in the expected data type. Extra spaces and text-formatted numbers are frequent causes of missed matches.

Can SUMIF criteria refer to a cell?
Yes. Put the criterion in a cell and use that reference as the criteria argument. For comparisons, combine an operator such as greater than with the cell reference using an ampersand.

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.