SUMIFS Excel Criteria Range: How to Set It Up

Formulas & functions · Updated November 18, 2026

In Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before adding a corresponding value. In the function, the sum range comes first, followed by one or more criteria-range and criteria pairs; each range must have the same shape and size as the sum range.

What a SUMIFS criteria range means

SUMIFS adds numbers only for records that meet specified conditions. A criteria range is the column or row containing the values Excel tests. For example, if you want to total sales for one region, the region column is the criteria range and the sales column is the sum range. Excel checks each region cell, and when it meets the condition, it adds the sales value in the corresponding position.

The criteria range is not the condition itself. The range might contain region names, dates, quantities, or other values; the criterion tells Excel what to look for in that range. A criterion can be a text value such as East, a number such as 100, a comparison such as greater than 100, or a reference to a cell that contains the value or comparison.

Think of the worksheet as a set of aligned records. Row 2 in the region range, product range, and sales range describes the same transaction. SUMIFS evaluates the relevant cells on that row together, then moves to the next row. This is why matching range dimensions matters.

How the arguments work

SUMIFS uses this argument order: sum range first, then criteria range 1 and criterion 1, followed by any additional criteria-range and criterion pairs. The sum range contains the numbers to add. Each criteria range contains the values to check for its matching condition. You can add multiple pairs when a total must satisfy multiple conditions.

This order is a frequent source of mistakes. SUMIFS starts with the cells to sum, whereas the older SUMIF function puts its criteria range first. When adapting a SUMIF formula, do not carry its argument order over to SUMIFS. Also, a criterion range must be supplied together with its criterion; an extra range on its own does not define a useful condition.

For example, a formula can ask Excel to sum the sales range where the region range equals East and the product range equals Widget. Each criterion-range pair adds another test. With multiple pairs, the tests are combined with AND logic: a row must pass every condition to contribute to the total.

A worked example with a criteria range

Suppose a worksheet has Region in A1, Salesperson in B1, Product in C1, and Sales in D1. Rows 2 through 6 contain these records: East, Ada, Widget, 120; East, Ben, Widget, 80; West, Ada, Widget, 150; East, Ada, Service, 40; and West, Ben, Widget, 90. The region and product columns are criteria ranges, and the sales column is the sum range.

With regions in A2:A6, products in C2:C6, and sales in D2:D6, use this formula to total Widget sales in East:

=SUMIFS(D2:D6,A2:A6,"East",C2:C6,"Widget")

The result is 200: the first and second records qualify, so Excel adds 120 and 80. It excludes the West Widget records because their region does not match, and excludes the East Service record because its product does not match. The formula illustrates how the same-position cells across the three ranges describe a single record.

To total Ada’s Widget sales regardless of region, use the salesperson and product columns as the two criteria ranges. With the same worksheet layout, the formula is:

=SUMIFS(D2:D6,B2:B6,"Ada",C2:C6,"Widget")

The result is 270, from Ada’s Widget sales of 120 in East and 150 in West. This example also shows that a criteria range does not need to be next to the sum range. It only needs to line up with it row by row.

Using text, numbers, operators, and cell references

A criterion may be a literal value or an expression. Text criteria are generally enclosed in straight quotation marks in a formula. Number criteria can be written directly, while comparison operators such as greater than or less than are combined with a value in a text criterion. Wildcards are useful when text is not an exact match.

  • Exact text: match a category such as East.
  • Comparison: test for values greater than 100 or dates on or after a chosen day.
  • Wildcard: use an asterisk for any number of characters or a question mark for one character. For example, a criterion beginning with North and followed by any text can match North, North-East, or North Zone.
  • Cell reference: use the value in a worksheet cell as the criterion, which makes a report easier to update without editing its formula.

With regions in A2:A6 and sales in D2:D6, suppose F1 contains the text East. This formula uses F1 as the criterion:

=SUMIFS(D2:D6,A2:A6,F1)

If you want to build a comparison around a cell reference, join the operator and the cell value with the ampersand. With quantities in B2:B20, amounts in C2:C20, and a threshold in F1, this sums amounts for quantities greater than the threshold:

=SUMIFS(C2:C20,B2:B20,">"&F1)

The displayed HTML escapes the greater-than sign and ampersand, but the formula entered in Excel uses the ordinary characters. This pattern is useful when users need to change a threshold without changing the formula. A comparison written as a fixed criterion, such as greater than 100, instead keeps that threshold inside the formula.

Multiple criteria and OR conditions

When a SUMIFS formula has multiple criteria pairs, Excel applies all of them to a row. For instance, requiring both a particular region and a particular product means only rows matching that region and that product are included. Adding another criteria range is appropriate when the worksheet stores another attribute you need to test, such as salesperson, department, or order status.

A request to match one value or another is different. Separate SUMIFS calculations can be added for two alternatives. With regions in A2:A6 and sales in D2:D6, this formula totals sales for either East or West:

=SUMIFS(D2:D6,A2:A6,"East")+SUMIFS(D2:D6,A2:A6,"West")

In this small example, each record has only one region, so the result is the sum of the two groups. Be careful when the alternatives can both match the same record, such as overlapping text conditions: adding separate totals can count that record twice. For a longer list of alternatives, a helper column, a PivotTable, or a formula design suited to the data may be clearer than a long chain of separate calculations.

Choosing and sizing criteria ranges

The safest approach is to select the same rows in every range, typically excluding the header row. If the sum range is D2:D100, use criteria ranges that also begin on row 2 and end on row 100. Ranges do not have to occupy the same columns, but they should cover the same records in the same order. A one-row shift can silently pair a criterion from one record with a sum value from another.

SUMIFS requires the sum range and each criteria range to have matching dimensions. A vertical range should be paired with ranges covering the same number of rows; a horizontal range should be paired with ranges covering the same number of columns. Mixing a short range with a longer one can return a #VALUE! error. If an error appears after you edit a formula, compare the start and end positions of every range.

For a small, fixed dataset, explicit ranges are easy to read. For a list that will grow, an Excel Table can keep formulas easier to maintain as records are added. Convert a data range to a table using Insert > Table, then refer to its named columns in formulas. Table references expand as rows are added, which helps avoid leaving new records outside a fixed range. Keep column headers distinct and descriptive so the references are understandable.

Whole-column references can be convenient in some worksheets, but they make the formula consider many more cells than a bounded range. For everyday workbooks, using a table or a sensible range that covers the data is often easier to review. The important point is not that the criteria column must touch the sum column, but that the corresponding entries remain aligned.

Dates and blank criteria

Dates in Excel are stored as serial values, even though cells display them in a calendar format. For date conditions, make sure the cells contain real Excel dates rather than text that merely looks like a date. A date criterion can be constructed from an operator and a date in a cell. If dates are in A2:A20, amounts are in D2:D20, and F1 contains a real date, this formula sums amounts for dates before F1:

=SUMIFS(D2:D20,A2:A20,"<"&F1)

To include the date in F1 as well as earlier dates, use a less-than-or-equal comparison. A common reporting choice is to use a cell for the cutoff date so the same formula can serve different periods. When a condition returns zero unexpectedly, check whether the supposed dates are stored as text and whether the chosen boundary should include or exclude the cutoff day.

Blank and nonblank conditions also come up in SUMIFS. A criterion of two quotation marks checks for blank cells, while a not-equal-to-blank condition checks for nonblank cells. Blank-looking cells can contain formulas that return an empty string, so distinguish between a genuinely empty cell and a cell whose formula displays nothing if the result matters. Test the data directly if the formula’s outcome seems inconsistent.

Common SUMIFS criteria-range problems

A result of zero does not necessarily mean the function is broken. It often means no row satisfies all criteria, or values that appear alike are stored differently. A number stored as text may not behave like a numeric value; leading or trailing spaces can stop text from matching; and dates stored as text can fail date comparisons. Check a few source cells and confirm their underlying values and data types.

  • Wrong argument order: place the sum range first, then alternate each criteria range with its criterion.
  • Misaligned or unequal ranges: confirm each range starts and ends on the intended records and has matching dimensions.
  • Criterion text mismatch: look for extra spaces, spelling differences, or a wildcard needed for partial matches.
  • Operator entered incorrectly: comparison operators used with a cell reference must be joined to that reference.
  • Unexpected zero: verify that every condition is true on at least one same row; multiple SUMIFS conditions are AND conditions.
  • Unexpected total: inspect duplicate records, broad wildcards, or OR calculations that may count overlapping records more than once.

Use Formula Bar to inspect the actual formula and select each range segment to see which cells it covers. You can also temporarily test a simpler version with one condition, then add conditions back one at a time. This helps identify whether the issue is the sum range, a particular criteria range, or the criterion itself.

SUMIFS compared with SUMIF and alternatives

SUMIF is suitable for a single condition; SUMIFS is designed for multiple conditions and can also handle a single condition. Their argument orders differ, so check the function before rearranging a formula. If the task is to count matching records rather than add values, COUNTIFS is the more relevant function. If you want to summarize several categories at once, a PivotTable can be easier to explore than writing a separate formula for each category.

SUMIFS is especially useful when the answer needs to remain in a worksheet cell and respond to changing criteria. A PivotTable can be a better fit for interactive summaries by category, while a filter is useful for viewing matching records rather than calculating a total. The best choice depends on whether you need a repeatable calculated result, an exploratory summary, or simply a filtered view of the source data.

Frequently Asked Questions

Does the criteria range go before or after the sum range in SUMIFS?
After it. SUMIFS takes the sum range first, then a criteria range followed by its criterion. This differs from SUMIF, whose criteria range is the first argument.

Do SUMIFS criteria ranges have to be the same size?
Yes. The sum range and every criteria range should have matching dimensions and align to the same records. They can be in different columns, but their corresponding rows must refer to the same data entries.

Can a SUMIFS criteria range be on another worksheet?
Yes. A criteria range may be on another worksheet, provided the formula refers to the intended cells and its dimensions match the sum range. Use a worksheet name and an exclamation mark in the reference, and use apostrophes around a sheet name that contains spaces.

Can SUMIFS use more than one criteria range?
Yes. Add a criteria-range and criterion pair for each additional condition. Excel includes a row only when it meets all the specified conditions.

Why does my SUMIFS formula return zero?
Possible causes include no row meeting every condition, a mismatch between text and numbers, hidden spaces, text-formatted dates, or an incorrectly aligned range. Test one condition at a time and inspect the source cells to narrow down the cause.

Can I use a wildcard in a SUMIFS criterion?
Yes. An asterisk matches any number of characters, and a question mark matches one character. Wildcards are useful for partial text matches, but a broad pattern can include more records than intended.

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.