SUMIF Criteria Examples: Text, Dates, Numbers, and More

In Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"Office",B2:B7) totals the amounts for Office and returns 350 in the sample data below. Criteria can also test numbers, dates, text patterns, or a value stored in another cell.

How SUMIF criteria work

SUMIF has three arguments: the cells to check, the condition to apply, and the cells to add. The third argument is optional. If you leave it out, Excel adds the cells in the range it checks, which is useful when the numbers being tested are also the numbers being totaled.

The examples use a small worksheet with category names in A2:A7, amounts in B2:B7, and transaction dates in C2:C7. The six rows are: Office, 120, January 5, 2026; Travel, 300, January 12; Office, 80, January 20; Food, 45, February 2; Office, 150, February 10; and Office Supplies, 60, March 1. Dates are actual Excel dates, not text that merely looks like a date.

The basic approach is to choose a condition that describes the records you want, then point SUMIF at the right columns. The criteria range and sum range should have matching shapes and corresponding rows. For example, checking A2:A7 and adding B2:B7 pairs each category with the amount on the same row.

Exact text criteria

For an exact text match, put the text in quotation marks in the formula. With categories in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"Office",B2:B7) returns 350: the three rows whose category is Office contain 120, 80, and 150. Excel’s text matching in SUMIF is not case-sensitive, so Office and OFFICE match the same entries.

An exact criterion is useful for totals by department, product, status, or other consistent labels. It does not require a special symbol for text. If the criterion is typed directly into the formula, include quotation marks around it. If the desired label is in a worksheet cell instead, use a cell reference as the criterion; that is covered below.

Be aware that an exact match is different from a partial match. The criterion Office does not include Office Supplies. Use a wildcard when you intend to include labels that start with, end with, or contain a word.

Numeric criteria and comparison operators

Numeric conditions can match one value or define a threshold. Comparison operators include greater than, less than, greater than or equal to, less than or equal to, and not equal to. When an operator and a number are written directly as a criterion, put both inside quotation marks.

With amounts in B2:B7, =SUMIF(B2:B7,">100",B2:B7) returns 570. It adds 120, 300, and 150 because those are the amounts strictly greater than 100; values equal to 100 would not qualify. If the intended rule is 100 or more, use the greater-than-or-equal operator instead.

For an exact numeric match, the criterion can be the number itself, without quotation marks. With amounts in B2:B7, =SUMIF(B2:B7,100,B2:B7) returns 0 for this sample because none of the amounts equals 100. This distinction matters: a threshold such as greater than 100 is not the same as an exact match for 100.

To exclude a value, use the not-equal operator. For example, a criterion of not equal to zero can be useful when zero-valued entries should not contribute to a total, although adding zero does not change a sum. If numbers appear to be ignored, check whether they are stored as text; formatting a text value as a number does not necessarily convert it into a real number.

Criteria stored in a cell

A cell reference makes a formula easier to update and reuse. If E2 contains the text Office, and categories are in A2:A7 while amounts are in B2:B7, =SUMIF(A2:A7,E2,B2:B7) returns 350. Changing E2 to Travel changes the result to 300 without editing the formula.

For a comparison operator stored in one cell and a numeric threshold in another, join them with the ampersand operator. Suppose E2 contains the operator text > and F2 contains the number 100. With amounts in B2:B7, =SUMIF(B2:B7,E2&F2,B2:B7) returns 570. The ampersand combines the operator and threshold into a criterion Excel can evaluate.

This pattern is handy for summary sheets where a reader can choose a category or threshold from an input cell. Keep the input clear: if a cell is meant to hold a category name, enter the category itself, not quotation marks around it. For a dropdown of permitted labels, use Data > Data Validation in Excel for Microsoft 365 on Windows.

Wildcards for partial text matches

Excel supports three wildcard characters in text criteria. An asterisk matches any number of characters, including none; a question mark matches exactly one character; and a tilde makes the following wildcard character literal. These patterns let a SUMIF include text that is not an exact match.

Office* matches text that begins with Office, such as Office and Office Supplies.
*Office* matches text containing Office anywhere in the label.
Off?ce matches a six-character word with any one character in the question-mark position.
~* searches for a literal asterisk rather than treating it as a wildcard.

With categories in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"Office*",B2:B7) returns 410. It adds the three Office amounts, 120, 80, and 150, plus the 60 for Office Supplies. This is a useful pattern for product families or labels that share a prefix, but it can include unintended rows if unrelated labels happen to begin the same way.

Wildcards are for text matching. For numeric ranges, use comparison operators rather than trying to represent a range with an asterisk. If a category itself contains an asterisk or question mark and you want that symbol treated literally, use a tilde before it in the criterion.

Date criteria and date ranges

Dates in Excel are stored as serial numbers, even when cells display them in a familiar date format. A robust formula builds a date criterion with the DATE function instead of relying on a typed date string whose interpretation might vary with regional settings.

To total dates on or after January 1, 2026, with dates in C2:C7 and amounts in B2:B7, =SUMIF(C2:C7,">="&DATE(2026,1,1),B2:B7) returns 755. All six sample transactions fall on or after that date, so the result is 120 + 300 + 80 + 45 + 150 + 60.

SUMIF supports one condition, so selecting a bounded period such as January requires two conditions: a start date and a date before the following month. With dates in C2:C7 and amounts in B2:B7, =SUMIFS(B2:B7,C2:C7,">="&DATE(2026,1,1),C2:C7,"<"&DATE(2026,2,1)) returns 500, the January amounts 120 + 300 + 80. SUMIFS is the related function for adding values that meet multiple criteria.

Using a start boundary inclusive and an end boundary exclusive is often practical for date ranges. It includes every time on the start date and avoids accidentally excluding entries that contain a time as well as a date. For a single date criterion, SUMIF can also compare dates to a date in a worksheet cell, provided the criterion is constructed with the appropriate comparison operator when needed.

Common SUMIF problems

When a formula returns zero or a surprising total, check the data and the ranges before changing the criteria. Common causes include dates or numbers stored as text, extra spaces in labels, a misspelled category, and criteria and sum ranges that do not line up row for row.

Check range sizes: Make sure the criteria range and sum range cover corresponding records. Using A2:A7 with B2:B8 can produce misleading results because the ranges differ in size.
Check text carefully: Leading or trailing spaces can make a label different from the one you expect. A wildcard may hide the mismatch, but cleaning the source data is usually a better long-term fix.
Check dates and numbers: A value stored as text may not behave like a true date or number in a comparison. Changing its cell format alone may not convert the underlying value.
Check quotation marks: Direct text criteria and criteria containing comparison operators need quotation marks in the formula. Cell references used as criteria do not need quotation marks around the reference.

Also remember that SUMIF does not distinguish uppercase from lowercase. If the task genuinely requires case-sensitive matching, SUMIF alone is not sufficient; a different formula approach is needed. Do not add extra conditions to a SUMIF criterion and expect them to work as separate tests—use SUMIFS when the total must meet multiple independent conditions.

SUMIF or SUMIFS?

Use SUMIF when one condition is enough, such as totaling all Office transactions or all amounts above 100. Use SUMIFS when several conditions must be true at the same time, such as a category plus a date window. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria, while SUMIF places the criteria range first.

For instance, a report might need the total for the Office category during January. That combines a text condition and two date boundaries, so SUMIFS is the natural choice. It is not a different kind of total; it is the multiple-condition counterpart to SUMIF. If a task asks for either one category or another, that is an OR condition, which may require adding separate SUMIF results or using another approach rather than simply stacking criteria in SUMIFS.

Both functions are available in Microsoft Excel for Microsoft 365 on Windows. The formulas shown here also use standard worksheet syntax; when entering formulas in Excel for Mac or Excel for the web, the core SUMIF and SUMIFS behavior is the same. Regional settings can affect the list separator, so some installations may require semicolons instead of commas.

Frequently Asked Questions

How do I use SUMIF for text that contains a word?
Use an asterisk on both sides of the word in the criterion. For example, the pattern *Office* matches labels containing Office anywhere. If you only want labels beginning with the word, put the asterisk after it.

Can SUMIF use more than one condition?
No. SUMIF evaluates one condition. Use SUMIFS for multiple conditions that must all be met, such as a category and a date range. For an either/or test, consider separate SUMIF calculations or a formula designed for OR logic.

Why does my SUMIF formula return zero?
Check that the criterion matches the source values, that numeric and date entries are stored as real values rather than text, and that the criteria and sum ranges cover the intended rows. Extra spaces and incorrect comparison-operator quotation marks are also common causes.

Does SUMIF distinguish uppercase and lowercase?
No. SUMIF text criteria are case-insensitive. Labels such as Office and OFFICE are treated as matching text for this function.

Can I use a date in a SUMIF criterion?
Yes. Excel stores real dates as numbers, so a date can be used as a criterion. For dependable comparisons, build the date with DATE and combine it with an operator when needed; use SUMIFS when you need both a start and end boundary.

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.