SUMIFS Excel: Sum Values That Meet Multiple Criteria

Formulas & functions · Updated November 18, 2026

SUMIFS is Excel’s conditional-sum function: it adds values only when all the conditions you specify are met. With sales in E2:E6, regions in A2:A6, and product names in C2:C6, for example, =SUMIFS(E2:E6,A2:A6,"West",C2:C6,"Widget") adds sales for West-region Widgets.

What SUMIFS does

SUMIFS is useful when a total needs to depend on more than one detail in a data table. You might want sales for one region and product, hours logged by one employee during a date range, or expenses assigned to a department and category. Instead of filtering the table and adding visible values by hand, the function evaluates each row against your conditions and sums the matching values.

Its key rule is that the conditions are combined with AND logic. A row contributes to the total only if it passes every condition supplied. If the conditions are “region is West” and “product is Widget,” a West Gadget is excluded, as is an East Widget. SUMIFS works in Microsoft 365 for Windows, Mac, and Excel for the web, and is also available in older Excel versions, including Excel 2007 and later.

How the arguments work

The first argument is always the range to add, called the sum range. After that, provide a criteria range and the condition to check in that range. Add another range-and-condition pair for each additional requirement. This order is easy to mix up because the older SUMIF function puts its criteria range before its sum range.

Each criteria range should correspond row by row to the sum range. If sales are in E2:E100, the region range should normally cover A2:A100, not A2:A90 or A1:A100. The ranges do not have to be in adjacent columns, but they should have matching dimensions so Excel tests the right record against each amount.

  • Sum range: the numeric values Excel should add.
  • Criteria range: the cells Excel should inspect for a condition.
  • Criteria: the required value, text, comparison, wildcard pattern, or cell reference.

Text conditions are usually placed in double quotes when written directly in a formula. A criterion can also refer to a worksheet cell, which makes it easier to change a report’s selections without editing the formula. SUMIFS is not case-sensitive for ordinary text comparisons, so “West” and “west” match the same text.

A basic example with a small sales table

Suppose a worksheet has Region in column A, Sales rep in B, Product in C, Date in D, and Sales in E. Rows 2 through 6 contain these records: West, Widget, $120 on January 5; West, Gadget, $80 on January 8; East, Widget, $150 on January 12; West, Widget, $200 on January 20; and West, Widget, $60 on February 2. The sales rep values do not affect this example.

With those records in A2:E6, the following formula sums the rows where Region is West and Product is Widget. Its result is 380, because it adds 120, 200, and 60; it excludes the West Gadget and East Widget.

With region in A2:A6, product in C2:C6, and sales amounts in E2:E6, use:

=SUMIFS(E2:E6,A2:A6,"West",C2:C6,"Widget")

For a reusable report, put the chosen region in G2 and the chosen product in H2. With the same source data in A2:A6, C2:C6, and E2:E6, this formula uses those cells as its criteria and still returns 380 when G2 is West and H2 is Widget:

=SUMIFS(E2:E6,A2:A6,G2,C2:C6,H2)

If you copy the formula to other cells, consider making the data ranges absolute references so they do not shift. For example, dollar signs in $E$2:$E$6 lock that range when copying; the selection cells G2 and H2 can remain relative if you want them to adjust.

Using numbers, operators, and text criteria

A criterion can test for an exact number or text value, or it can use a comparison operator. Common operators include 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 value typed directly into the formula, the whole criterion is text, such as ">500". When the comparison value is in a cell, join the operator to that reference with the ampersand character.

For example, suppose a report needs to add sales above a threshold entered in G2 while still checking a region in G1. With region values in A2:A6 and sales in E2:E6, the criteria pair can use the region cell and a greater-than comparison tied to the threshold cell. This keeps the threshold editable without changing the formula. A condition such as not equal to a value can be used the same way.

For partial text matches, SUMIFS supports two wildcard characters: an asterisk (*) matches any number of characters, and a question mark (?) matches one character. A criterion such as "North*" can match North, Northern, or North-East. To search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde (~). Use wildcards when names have predictable beginnings or endings, but be aware that a broad pattern can include records you did not intend to count.

Adding a date range

Dates are stored as serial numbers in Excel, even though cells usually display them in a readable date format. To sum records within a period, apply a lower-bound criterion and an upper-bound criterion to the date column. For a period that includes both endpoints, use “greater than or equal to” the start date and “less than or equal to” the end date. The operator is joined to a date cell so Excel compares against the actual date value.

Using the sample table, put January 1, 2026, in G3 and January 31, 2026, in G4. With dates in D2:D6, regions in A2:A6, and sales in E2:E6, this formula totals West sales dated in January. It returns 320: the January West Widget sales of 120 and 200, plus no other West sales in that month.

=SUMIFS(E2:E6,A2:A6,"West",D2:D6,">="&G3,D2:D6,"<="&G4)

For reliable results, make sure the source dates and the boundary cells are real Excel dates rather than text that merely looks like a date. If a report covers a full month and the source dates may include times, a safer pattern is to include dates on or after the first day and dates before the first day of the next month. That avoids missing entries recorded after midnight on the final day. Keep the two comparisons on the same date range, with the appropriate start and end bounds.

Handling OR conditions

Because criteria pairs inside one SUMIFS are joined with AND, one formula does not express “this region or that region” as two alternatives for the same field. A common solution is to add the results of two SUMIFS calculations. In the sample data, suppose the goal is to sum Widget sales from either East or West. With regions in A2:A6, products in C2:C6, and sales in E2:E6, the formula returns 530: 150 from East plus 380 from West.

=SUMIFS(E2:E6,A2:A6,"East",C2:C6,"Widget")+SUMIFS(E2:E6,A2:A6,"West",C2:C6,"Widget")

This approach is clear when the alternatives are a short list. Take care if the alternatives can overlap: adding separate totals can count the same record more than once. When the conditions are mutually exclusive values in one column, such as East or West, each matching row belongs to just one total. For a large or changing set of alternatives, a helper column or a pivot table may be easier to maintain.

SUMIFS compared with SUMIF and other tools

Use SUMIFS when the total depends on multiple conditions. Use SUMIF when there is only one condition; its argument order differs, so check the order before converting a formula. SUMIFS can also handle a single condition, but the plural function is often easier to extend when a report may gain another criterion later.

SUMIFS is a good fit for a straightforward total from a well-structured table. A PivotTable is often more convenient when you need to explore totals across several categories, compare many periods, or rearrange the report interactively. A filter followed by a manual total can answer a quick one-off question, but it is less repeatable and can be misleading if the range changes. For a formula-driven summary with a few defined rules, SUMIFS keeps the logic visible in the cell.

Tables can make this kind of report easier to maintain because their references expand as rows are added. In Microsoft 365, inserting a table through Insert > Table allows formulas to use column names rather than fixed cell addresses. Structured references can be easier to read, although they may look unfamiliar if you are used to A1-style references.

Common SUMIFS problems and fixes

  • The result is zero: check that every criterion exactly matches the source values, and verify that number and date criteria use the right data types. A number stored as text may not behave like a numeric value.
  • The result is too high or too low: confirm each criteria range lines up with the sum range and covers the intended records. Check that date boundaries include the desired dates and that wildcard criteria are not too broad.
  • A text comparison misses a match: look for extra spaces, inconsistent spelling, or hidden characters. Excel’s ordinary text matching ignores letter case but does not generally ignore leading or trailing spaces.
  • A comparison cell does not work: when a criterion uses an operator and a cell reference, join them together rather than typing the operator separately from the value.
  • The formula is rejected: check the parentheses, commas, quotation marks, and range sizes. Excel installations configured for some regional settings may use semicolons as argument separators instead of commas; follow the separator your Excel expects when entering a formula.

Also remember that SUMIFS adds numeric values, not text that resembles numbers. If a source amount is stored as text, convert or correct the source data rather than relying on the total formula to interpret it. Errors in referenced data can also affect the result, so inspect the underlying cells when a formula displays an error.

Frequently Asked Questions

Does SUMIFS use AND or OR logic?
Conditions inside one SUMIFS are combined with AND: a row must satisfy every criterion. For OR alternatives, add separate SUMIFS results, taking care not to count overlapping records twice.

Does the sum range have to be the first argument?
Yes. SUMIFS starts with the range to sum, followed by pairs of criteria ranges and criteria. This differs from SUMIF’s argument order and is a frequent source of mistakes.

Can SUMIFS use dates as criteria?
Yes. Excel can compare actual date values using criteria such as on or after a start date and on or before an end date. Confirm that the source dates and any date cells used for the boundaries are real dates, not text.

Why does SUMIFS return zero even though matching rows are visible?
Common causes include a criterion that does not exactly match the source, numbers or dates stored as text, extra spaces, or criteria ranges that do not align with the sum range. Check a few source cells and the formula’s ranges before changing the conditions.

Can SUMIFS match part of a text value?
Yes. Use an asterisk for any number of characters or a question mark for a single character. A tilde escapes a wildcard when you need to match the literal character itself.

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.