Excel SUMIFS With Multiple Criteria: A Practical Guide

Formulas & functions · Updated November 19, 2026

With sales in D2:D7, regions in B2:B7, and products in C2:C7, use =SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Tea") to add sales where the region is East and the product is Tea; with the sample records below, the result is 320. SUMIFS accepts multiple range-and-criteria pairs, and by default every condition must be true for a row to be included.

How SUMIFS evaluates multiple criteria

SUMIFS is designed to total values after checking corresponding rows against one or more conditions. Its argument order is important: first give it the range to add, then give it a criteria range and its matching criterion, repeating that pair as needed. The criteria ranges must line up with the sum range: in an ordinary worksheet, use ranges with the same number of rows and columns.

For the example, imagine these records in rows 2 through 7: East, Tea, 120; East, Coffee, 80; West, Tea, 150; East, Tea, 90; West, Coffee, 60; and East, Tea, 110. The formula in the opening paragraph checks each row in B and C. Only rows 2, 5, and 7 meet both conditions, so the total is 120 + 90 + 110 = 320.

SUMIFS uses AND logic between separate criteria pairs. A row must satisfy the region test and the product test, not just one of them. It can handle up to 127 criteria-range/criterion pairs, though a formula with many conditions may be harder to maintain than a well-organized summary or PivotTable.

Build a formula one condition at a time

Start with the sum range: identify the numeric values you want to add. Then choose a range that contains the field you want to test, followed by the criterion for that field. Add another range-and-criterion pair for every additional condition.

  1. Keep each data field in its own column, such as date, region, product, and amount.
  2. Select the numeric amount range as the first SUMIFS argument.
  3. Add the first criteria range and its condition, then repeat for the other conditions.
  4. Check that every range covers the same rows and that the formula uses commas between arguments.

For example, with amounts in D2:D100, regions in B2:B100, and products in C2:C100, the formula =SUMIFS(D2:D100,B2:B100,"East",C2:C100,"Tea") totals only records matching both labels. In Excel for Microsoft 365 on Windows, enter the formula in a blank cell and press Enter. The same formula syntax works in current Excel for Mac and Excel for the web; ribbon locations for other tasks may differ, but SUMIFS itself is not tied to a particular platform.

Use cells as criteria

Hard-coded criteria are convenient for a quick calculation, but references make a report easier to reuse. Put a region name in F2 and a product name in G2. With sales in D2:D100, regions in B2:B100, and products in C2:C100, use =SUMIFS(D2:D100,B2:B100,F2,C2:C100,G2). Change F2 or G2 and the total updates without editing the formula.

Criteria can also test numbers with comparison operators. The operator is written as text inside quotation marks, and when the threshold comes from a cell, join the operator to the cell reference with the ampersand. With quantities in D2:D100, departments in B2:B100, and a minimum quantity in F2, use =SUMIFS(D2:D100,B2:B100,"Sales",D2:D100,">="&F2). This adds values from the Sales department only when the value in D is at least the threshold in F2. A criterion such as "<100" means less than 100; "<>Closed" means not equal to Closed.

Combine criteria with OR logic

Multiple pairs in one SUMIFS represent AND, not OR. To total Tea sales from either East or West, add two SUMIFS results. With regions in B2:B7, products in C2:C7, and sales in D2:D7, use =SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Tea")+SUMIFS(D2:D7,B2:B7,"West",C2:C7,"Tea"). For the sample records, that returns 470: East Tea totals 320 and West Tea contributes 150.

This approach is straightforward when there are only a few alternatives. Be alert to overlap: if the separate formulas describe conditions that can match the same row, adding their totals counts that row more than once. For alternatives that are mutually exclusive, such as a region being either East or West in one region column, the sum is safe. In Microsoft 365, another option for a short list of alternatives in one field is to pass an array of criteria to SUMIFS and wrap the result in SUM. With regions in B2:B7, products in C2:C7, and sales in D2:D7, this formula totals Tea for East and West: =SUM(SUMIFS(D2:D7,B2:B7,{"East","West"},C2:C7,"Tea")). The outer SUM combines the two results; the explicit addition method is often easier to read and troubleshoot.

Handle dates and date ranges

Excel stores valid dates as serial numbers, so date criteria should compare actual date values rather than text that merely looks like a date. Use DATE to build an unambiguous date, or refer to a cell containing a real Excel date. To include all dates in January 2026, set the lower limit to January 1 and the upper limit to February 1, using “greater than or equal to” for the first and “less than” for the second. The exclusive upper limit includes any time of day on January 31.

Assume dates are in A2:A100, regions in B2:B100, and amounts in D2:D100. Use =SUMIFS(D2:D100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1),B2:B100,"East") to total East amounts recorded during January 2026. The ampersands join each comparison operator to a date value. If the results unexpectedly omit records, check whether the date column contains genuine date/time values or text imported from another system.

Use wildcards for text criteria

For text conditions, SUMIFS supports two common wildcards: an asterisk matches any sequence of characters, and a question mark matches one character. For example, "Tea*" matches text beginning with Tea, including Tea and Teapot. "?at" matches a three-character value ending in “at,” such as Cat or Hat. Excel comparisons with text criteria are generally not case-sensitive, so Tea and TEA normally match the same criterion.

To match a literal wildcard character rather than treat it as a wildcard, precede it with a tilde. For instance, "~*" searches for an actual asterisk. Wildcards are useful when labels have predictable prefixes or suffixes, but they can also match more records than expected. If exact matching matters, use the full label rather than a broad pattern.

Common SUMIFS errors and how to fix them

A formula that returns zero or an unexpected total is often syntactically valid but has a mismatch in the data or ranges. Check the following before rewriting it:

  • Range sizes differ: sum_range and every criteria_range should cover matching rows. For example, D2:D100 paired with B2:B99 can produce an error.
  • Numbers are stored as text: an amount that looks numeric but was imported as text may not add as expected. Convert the source values to numbers and check for stray spaces or symbols.
  • Criteria text differs: an extra space, spelling variation, or inconsistent label can prevent a match. TRIM can help clean imported text, but do not assume visually similar values are identical.
  • Dates are text: a displayed date may not be a real date value. Test the source data and correct its type before applying date boundaries.
  • Operators are not joined correctly: a condition based on a cell needs a quoted operator concatenated with that reference, such as ">="&F2, rather than an operator typed outside the criteria argument.
  • Unexpected blanks: an empty criterion or a blank-looking cell can be confusing, especially if cells contain formulas returning an empty string. Test a small range and inspect the source values to confirm what should count as blank.

For diagnosis, temporarily reduce the formula to one criterion pair, confirm that it returns a plausible total, and then add the remaining pairs one at a time. This isolates which condition or range is responsible. Also verify that the sum range contains the values you intend to total; a shifted column reference is an easy mistake to overlook.

SUMIFS compared with related tools

Use SUMIFS when the desired result is a sum and the conditions are straightforward. SUMIF handles a single condition; SUMIFS is the multi-condition counterpart and can also handle a single condition. Although their argument order differs, SUMIFS puts the sum range first, which is useful to remember when converting formulas.

COUNTIFS is for counting rows that meet multiple conditions, not adding their amounts. A PivotTable is often better when readers need to explore totals by several categories or change the grouping interactively. SUMPRODUCT can express more involved logic, but it is usually less readable for a basic conditional total. Choosing the simplest tool that fits the question makes a workbook easier for someone else to audit.

Frequently Asked Questions

Can SUMIFS use more than two criteria?
Yes. Add another criteria-range and criterion pair for each condition, up to 127 pairs. Make sure all ranges cover corresponding rows and that each criterion expresses a condition you actually want to require.

Does SUMIFS use AND or OR between criteria?
It uses AND between separate criteria pairs: a row must pass all of them. For OR conditions, add separate SUMIFS calculations for the alternatives or use an array of alternatives where appropriate, taking care not to count overlapping rows twice.

Can I use greater than or less than in SUMIFS?
Yes. Put a comparison operator and value in a text criterion, such as ">100". To compare against a cell, join the quoted operator to the reference with ampersand, for example ">"&F2.

Why does my SUMIFS formula return zero?
Common causes include a criterion that does not exactly match the source data, text stored where a number or date is expected, or criteria ranges that do not correspond to the intended records. Check each condition separately and inspect the underlying values.

Can SUMIFS match part of a word?
Yes. Use an asterisk for any number of characters or a question mark for one character. For example, "North*" matches text beginning with North. Use a tilde before a wildcard when you need to match that character literally.

Related

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.