SUMIFS in Excel: Multiple Criteria, Examples and Tips

Formulas & functions · Updated November 18, 2026

Yes. In Excel, SUMIFS adds numbers only when multiple conditions are met; each criteria pair can test a different range, and all pairs must match for a row to be included. For example, it can total sales where the region is East and the product is Hardware.

What SUMIFS does

SUMIFS is Excel’s function for conditional totals. It is useful when a simple sum is too broad: you might want sales for one region and product, expenses within a date range, or hours logged by a particular employee on a particular project.

The key idea is that each criteria range is checked against its corresponding criterion. Excel includes a row in the total only when that row passes every test. This is an AND relationship: region must be East and product must be Hardware. The function is available in Microsoft 365 for Windows, Mac, and Excel for the web, as well as many earlier Excel versions.

SUMIFS syntax and argument order

The arguments begin with the range to add, followed by one or more pairs: a range to check and the condition to apply to it. In other words, the order is sum range first, then criteria range and criterion, repeated as needed. Excel allows up to 127 criteria-range-and-criterion pairs.

That order is easy to mix up if you know SUMIF, which puts its criteria range before its sum range. With SUMIFS, the sum range always comes first. Each criteria range should have the same dimensions as the sum range—for example, use rows 2 through 100 for all three ranges, rather than summing 2:100 and checking 2:99.

A criterion can be a number, text, a cell reference, an expression such as a comparison operator combined with a value, or a wildcard pattern. Text criteria are generally not case-sensitive, so East and EAST match the same text.

Example: combine two criteria

Suppose a worksheet has dates in A2:A6, regions in B2:B6, products in C2:C6, and sales amounts in D2:D6. The five rows contain January 3, East, Hardware, 120; January 5, East, Software, 80; January 10, West, Hardware, 150; January 12, East, Hardware, 200; and February 2, East, Hardware, 90.

With sales in D2:D6, regions in B2:B6, and product names in C2:C6, this totals rows where the region is East and the product is Hardware:

=SUMIFS(D2:D6,B2:B6,"East",C2:C6,"Hardware")

The result is 410: Excel adds 120 and 200, the two rows that meet both conditions. It excludes the East Software row because the product does not match, the West Hardware row because the region does not match, and the February row only if a date condition is also added. If you plan to change the criteria, put East in F2 and Hardware in G2 instead.

With sales in D2:D6, regions in B2:B6, products in C2:C6, the region criterion in F2, and product criterion in G2, use:

=SUMIFS(D2:D6,B2:B6,F2,C2:C6,G2)

Cell references make a report easier to reuse: change F2 or G2 and the total updates, without editing the formula. Copying the formula down a report can also be useful; use absolute references such as $B$2:$B$6 if the source ranges should stay fixed while the criteria references move.

Use comparison operators and dates

Criteria can use comparison operators including greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). When an operator is combined with a cell reference, join them with &. For example, a criterion that means “at least the value in F2” is written as ">="&F2.

With amounts in D2:D6 and the same amount range as the criterion range, this sums values of at least 100:

=SUMIFS(D2:D6,D2:D6,">=100")

The result is 470: 120 + 150 + 200. The values 80 and 90 are below 100. A text criterion containing an operator and a number should be enclosed in quotation marks, as shown.

Date criteria need a little care because Excel stores dates as serial values, even when the cells are formatted to display dates. Avoid typing a date in a format that might be interpreted differently by regional settings. Instead, refer to a cell containing a real Excel date or construct a date with DATE.

With dates in A2:A6, sales in D2:D6, and a real January 1, 2026 date in F2, this sums January sales by including dates on or after F2 and before the first day of the next month:

=SUMIFS(D2:D6,A2:A6,">="&F2,A2:A6,"<"&DATE(YEAR(F2),MONTH(F2)+1,1))

The result is 550: the four January amounts, 120 + 80 + 150 + 200. Using a less-than boundary for February 1 is often safer than trying to include the last moment of January 31. It also works when the date column contains times, because a timestamp later on January 31 is still earlier than February 1.

Text criteria, wildcards, and blank cells

For an exact text match, provide the text as a criterion, such as "Hardware". To match part of a text value, Excel supports two wildcards: the asterisk (*) represents any number of characters, and the question mark (?) represents one character. A tilde (~) is used to search for a literal wildcard character rather than treating it as a pattern.

With product names in C2:C6 and sales in D2:D6, this totals all products whose names begin with “Hard”:

=SUMIFS(D2:D6,C2:C6,"Hard*")

For the sample rows, the result is 560: the Hardware sales of 120, 150, 200, and 90. The asterisk allows any characters after “Hard.” By contrast, "H?rdware" matches a name with exactly one character in place of the question mark.

Blank criteria can be useful for records that have not been assigned a category, while "<>" can be used to test for nonblank cells. However, cells containing formulas that return an empty string can behave differently from genuinely empty cells in some criteria checks. If blank handling matters, test the formula against the actual data and consider adding a helper column that labels missing values explicitly.

How to handle OR conditions

Multiple criteria pairs in one SUMIFS formula mean AND, not OR. If the requirement is “East or West,” one straightforward method is to add two separate totals. With regions in B2:B6 and sales in D2:D6, this sums sales for either of those two distinct region names:

=SUMIFS(D2:D6,B2:B6,"East")+SUMIFS(D2:D6,B2:B6,"West")

The result is 640, the sum of all five sample sales. This approach is clear when there are only a few alternatives. If the conditions can overlap—for example, “contains the word East” or “starts with E”—adding separate totals could count a row twice. Choose conditions that cannot overlap, or use a method that explicitly avoids duplicates.

For a longer list of categories, a PivotTable or a small summary table may be easier to maintain than a long chain of added formulas. The important distinction is logical: putting Region = East and Region = West as two criteria pairs in one SUMIFS asks Excel to find a row equal to both at once, which normally returns no matching rows. It does not mean either region.

Common SUMIFS problems and fixes

  • Ranges are different sizes: Make the sum range and every criteria range cover the same rows and columns. Inconsistent endpoints can cause a #VALUE! error or an incorrect setup.
  • The sum range is in the wrong position: Remember that SUMIFS starts with the sum range. A formula copied from a SUMIF example may have its arguments in the wrong order.
  • Numbers or dates are stored as text: A value that looks like a number may not be numeric data. Check the source cells and convert text values if necessary; changing number formatting alone does not convert text into numbers.
  • The operator is not joined to the cell reference: To compare against a value stored in F2, use a criterion such as ">="&F2, not a quoted expression that literally contains the characters F2.
  • There are extra spaces or unexpected text: “East” and “East ” may fail to match. Clean imported data with tools such as TRIM where appropriate, or inspect the source values directly.
  • The result is zero when it should not be: Verify each criterion separately, then check that the criteria cells contain the expected values and that the sum cells are numeric. Also confirm that dates are genuine dates and not text that only looks like one.

When a formula appears correct but produces an unexpected total, test one condition at a time and compare the matching rows with the worksheet. This narrows down whether the issue is a criterion, a data type, a range boundary, or an assumption about AND versus OR logic.

When SUMIFS is the right choice

Use SUMIFS when the result you need is a sum and the conditions are straightforward comparisons across worksheet ranges. It is usually easier to read and maintain than a more elaborate formula for common reports. Keep source columns consistent, use clear headings, and put changeable criteria in cells so other people can see what drives the total.

A PivotTable is a strong alternative when you want to explore totals across several categories, change the grouping interactively, or produce a reusable summary without writing a separate formula for every combination. A table with filters may be enough when you only need to inspect matching records rather than calculate a separate total. More complex row-by-row logic may call for helper columns or another formula approach, but start with SUMIFS if each condition can be expressed as a range and a criterion.

On Microsoft 365 for Windows, you can type the formula directly into a cell or use the Insert Function button beside the formula bar to find SUMIFS and enter its arguments. The formula works in Excel for Mac and Excel for the web too. Ribbon layouts differ slightly by platform, and formula argument separators can depend on regional settings; if your Excel uses semicolons as separators, follow the separator shown by that installation.

Frequently Asked Questions

Can SUMIFS use more than two criteria?
Yes. Add another criteria-range-and-criterion pair for each additional condition, up to 127 pairs. All the conditions in one formula must be true for a row to contribute to the total.

Does SUMIFS use AND or OR logic?
It uses AND logic between criteria pairs. To total rows matching one condition or another, add separate SUMIFS results when the alternatives do not overlap, or use a summary method suited to the data.

What is the difference between SUMIF and SUMIFS?
SUMIF is designed for one condition, while SUMIFS supports multiple conditions. Their argument order also differs: SUMIFS puts the sum range first, while SUMIF puts its criteria range first.

Can SUMIFS criteria refer to cells?
Yes. Put the condition in a cell and use that reference as the criterion. For operator-based comparisons, join the operator to the reference with &, such as ">="&F2.

Why does SUMIFS return zero?
Common causes include criteria that do not exactly match the source data, numbers or dates stored as text, extra spaces, or ranges that do not cover the intended records. Check the data types and test each condition against the relevant column.

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.