SUMIF Excel Examples With Two Criteria to Match Rows

To total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8, products in B2:B8, and sales in E2:E8, the formula =SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$B$2:$B$8,"Laptop") returns 540, the sum of East-region Laptop sales.

Why two conditions call for SUMIFS

The function name is an easy source of confusion: SUMIF handles one condition, while SUMIFS handles multiple conditions. In SUMIFS, the range to add comes first, followed by pairs of a criteria range and a criterion. Each extra pair adds another condition. Excel includes a row only when it satisfies every supplied criterion, so the conditions work as AND tests.

For example, “Region is East” and “Product is Laptop” means both facts must be true on the same row. SUMIFS adds that row’s sales value and skips rows that meet only one condition. You can add more criteria pairs if a calculation also needs to match a representative, status, date, or other field.

This distinction matters because adding a second argument pair to SUMIF does not create a valid two-condition SUMIF formula. If a worksheet or tutorial says “SUMIF with two criteria,” the practical answer is usually to use SUMIFS. Excel for Microsoft 365 on Windows, Mac, and the web supports these functions; formula logic is the same, though regional settings can use semicolons instead of commas as argument separators.

A basic two-criteria example

Suppose a sales list has Region in column A, Product in column B, Sales representative in C, Date in D, and Sales in E. The relevant sample rows are East/Laptop/Ana/$120, East/Monitor/Ben/$80, West/Laptop/Ana/$150, East/Laptop/Ben/$200, East/Laptop/Ana/$90, West/Monitor/Ana/$110, and East/Laptop/Ana/$130. The first formula totals East Laptop sales, regardless of representative or date, and returns $540: 120 + 200 + 90 + 130.

With those fields in A2:A8, B2:B8, and E2:E8, the formula is =SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$B$2:$B$8,"Laptop"). The dollar signs lock the ranges if you later copy the formula elsewhere. They are optional when the formula will stay in one cell.

Criteria can also refer to cells, which is useful for making a summary table interactive. If G2 contains East and H2 contains Laptop, and the data layout remains unchanged, =SUMIFS($E$2:$E$8,$A$2:$A$8,G2,$B$2:$B$8,H2) returns $540. Change G2 or H2 to recalculate the total without editing the formula. Cell references are especially handy when a report has several regions or products to summarize.

Using numeric criteria and comparison operators

A criterion does not have to be an exact text value. You can ask for values greater than, less than, equal to, or different from a threshold. For criteria such as “at least 10,” put the operator and number in quotation marks as one criterion. If the threshold is stored in a cell, join the operator to that cell with &.

For a separate order table with Category in A2:A5, Units in B2:B5, and Amount in C2:C5, assume the rows are Office/8/$40, Office/12/$120, Office/20/$180, and Tech/15/$300. The formula =SUMIFS($C$2:$C$5,$A$2:$A$5,"Office",$B$2:$B$5,">=10") returns $300: it adds $120 and $180 because both Office rows have at least 10 units.

With the same layout and a minimum-unit threshold in E1, the formula =SUMIFS($C$2:$C$5,$A$2:$A$5,"Office",$B$2:$B$5,">="&E1) returns $300 when E1 is 10. This is a useful pattern for changing a cutoff from a worksheet control cell. For a range between two limits, add two criteria pairs for the same numeric range: one for the lower bound and one for the upper bound.

Matching dates and date ranges

Excel stores dates as serial numbers, so a date criterion should compare actual date values rather than text that merely looks like a date. To total January 2026 sales for the East region, set a start boundary of January 1 and an exclusive end boundary of February 1. The exclusive upper boundary includes every time on January 31 if the date cells also contain times.

With Region in A2:A8, dates in D2:D8, and Sales in E2:E8 as in the sample, the formula =SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$D$2:$D$8,">="&DATE(2026,1,1),$D$2:$D$8,"<"&DATE(2026,2,1)) returns $530. It includes the East sales of $120, $80, $200, and $130 dated in January, and excludes the February sale of $90.

Using DATE(year,month,day) avoids ambiguity between regional date formats such as month/day/year and day/month/year. If dates are entered in cells, build the criteria by concatenating the operator with a cell reference instead. Check that the source cells contain real Excel dates: text imported from another system may look like a date but fail a date comparison.

Wildcards and text matching

For text criteria, an exact criterion such as "Laptop" matches that text value. Wildcards let you match a pattern: an asterisk represents any number of characters, and a question mark represents one character. For example, “Lap*” can match Laptop or other text beginning with “Lap.”

With Region in A2:A8, Product in B2:B8, and Sales in E2:E8, =SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$B$2:$B$8,"Lap*") returns $540 for the sample list. This is helpful when product labels have suffixes or longer descriptions. If the text you need to match contains a literal asterisk or question mark, precede that wildcard character with a tilde in the criterion so Excel treats it as ordinary text.

Extra spaces can prevent a text criterion from matching what looks like the same label. For example, “East ” with a trailing space may not match “East.” Imported data can also contain nonbreaking spaces or inconsistent spelling. If exact-text totals unexpectedly return zero, inspect the source values, standardize labels, and check for hidden spaces before changing the sum formula.

AND versus OR criteria

Different criteria pairs in one SUMIFS formula are joined as AND. To find Ana’s Laptop sales, the row must have Ana in the representative column and Laptop in the product column. With representatives in C2:C8, products in B2:B8, and sales in E2:E8, =SUMIFS($E$2:$E$8,$C$2:$C$8,"Ana",$B$2:$B$8,"Laptop") returns $490: 120 + 150 + 90 + 130.

OR logic usually requires adding separate SUMIFS results. To total East sales for either Laptop or Monitor, with Region in A2:A8, Product in B2:B8, and Sales in E2:E8, use =SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$B$2:$B$8,"Laptop")+SUMIFS($E$2:$E$8,$A$2:$A$8,"East",$B$2:$B$8,"Monitor"). It returns $620: $540 for East Laptop plus $80 for East Monitor.

Take care when adding separate totals: if the criteria groups overlap, a row could be counted twice. The Laptop and Monitor groups above are distinct, so there is no overlap. For complicated OR rules, a helper column or a formula built around a more explicit set of conditions can be easier to audit than a long chain of additions.

Common errors and practical checks

SUMIFS can be correct syntactically and still return an unexpected number. Check these points before rebuilding the formula:

Argument order: SUMIFS starts with the sum range, then takes criteria-range/criterion pairs. This differs from SUMIF’s argument order.
Range dimensions: The sum range and every criteria range should cover corresponding rows and have the same number of cells. A mismatch can produce an error or misleading results.
Number stored as text: A number imported as text may not behave like a numeric value in a comparison. Confirm the source type and convert the data if necessary.
Criteria operators: Comparisons such as greater-than-or-equal need a quoted operator and, for a cell-based threshold, concatenation with &.
Absolute references: Use dollar signs when copying a formula so the source ranges do not shift unintentionally.
Hidden criteria differences: Check spaces, spelling, date values, and wildcard characters when a formula returns zero despite apparent matches.

When a formula gives a plausible but questionable total, filter the source table to the same conditions and compare the visible rows. Also test one criterion at a time. If the region-only sum is right but the product condition changes it unexpectedly, the issue is likely in the product values or the selected criteria range rather than the sales column.

Frequently Asked Questions

Can SUMIF use two criteria?
Not directly. SUMIF is designed for one condition. Use SUMIFS for two or more conditions that must all be true on a row.

Does SUMIFS use AND or OR logic?
Criteria pairs in a single SUMIFS formula use AND logic. For OR conditions, add separate SUMIFS formulas when the groups do not overlap, or choose a more explicit approach if overlap could double-count rows.

Can the two criteria be in cells instead of typed into the formula?
Yes. Put each desired condition in a worksheet cell and use those cell references as the criteria arguments. This makes it simple to reuse the formula for different regions, products, dates, or thresholds.

Why does my two-criteria formula return zero?
Common causes include a misspelled or space-padded label, a date stored as text, a criterion that does not match the cell’s underlying value, or a criteria range pointed at the wrong column. Check each condition against the source rows separately.

Can SUMIFS match a date range?
Yes. Use two criteria on the same date range, one for dates greater than or equal to the start and another for dates less than the day after the end. This boundary pattern also handles date-time values reliably.

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.