For multiple criteria in Excel, use SUMIFS rather than SUMIF. With dates in A2:A8, regions in B2:B8, products in D2:D8, and sales in E2:E8, sum East-region Coffee sales with =SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Coffee"); the example below returns 250.
Why SUMIF Does Not Take Multiple Criteria
SUMIF is designed to test one condition: it checks a range, then adds the corresponding values from a sum range when that condition is met. Its syntax has room for one criteria range and one criterion. Adding a second condition inside the same SUMIF call does not work as a way to require both conditions.
For example, a report might need to total sales only when the region is East and the product is Coffee. Those are two separate tests, and both must be true for a row to count. Excel’s function for that job is SUMIFS. Its name ends in “S” because it can evaluate multiple criteria.
This distinction matters when reading online examples: people often say “SUMIF with multiple criteria” when they mean “sum values that meet multiple conditions.” The intended result is clear, but the appropriate function is usually SUMIFS.
Set Up a Small Example Table
Suppose a worksheet has these columns: transaction date in A, region in B, salesperson in C, product in D, and sales amount in E. Rows 2 through 8 contain the following records:
- Jan 3, East, Maya, Coffee, 120
- Jan 4, East, Leo, Tea, 80
- Jan 5, West, Maya, Coffee, 150
- Jan 9, East, Maya, Coffee, 70
- Jan 12, West, Leo, Tea, 90
- Jan 15, East, Leo, Coffee, 60
- Jan 15, East, Maya, Tea, 40
The values in the examples are illustrative. The region-and-product total for East and Coffee is 120 + 70 + 60, or 250. If your worksheet uses different columns, change the ranges to point to the equivalent fields. Each criteria range must cover the same rows as the sum range; otherwise, Excel may return an error or an unintended result.
Use SUMIFS for Conditions That Must All Match
The syntax for SUMIFS puts the values to add first, followed by pairs of criteria ranges and criteria. In the example table, sales are in E2:E8, region is in B2:B8, and product is in D2:D8. With that layout, the formula is:
=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Coffee")
It returns 250. Excel checks each row in the region range for East and the corresponding row in the product range for Coffee. It adds the amount from column E only when both tests are true. In other words, criteria supplied as separate pairs in one SUMIFS call are combined with AND logic.
For three conditions, add another range-and-criterion pair. For instance, with the same table layout, this totals Coffee sales in the East region made by Maya:
=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Coffee",C2:C8,"Maya")
The result is 190, from the Jan 3 and Jan 9 records. You can use up to 127 criteria-range/criteria pairs in SUMIFS. In practice, a few well-chosen conditions are easier to audit than a very long formula.
Combine Criteria for OR Logic
Not every request means that all criteria must be true. “East or West region, for Coffee” is an OR condition on region: a row may match East or West, while Coffee must still match. One straightforward approach is to add two SUMIFS results. With the example table’s sales, region, and product ranges, use:
=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Coffee")+SUMIFS(E2:E8,B2:B8,"West",D2:D8,"Coffee")
This returns 400: the East Coffee total of 250 plus the West Coffee total of 150. This method is easy to understand and works well when there are only a few alternatives. For many choices, keep the possible criteria in worksheet cells or use a more structured approach rather than making a long chain of additions.
Be careful when mixing AND and OR. Separate criteria pairs in one SUMIFS call require all those conditions to match. Adding separate results expresses alternatives. If the alternatives overlap—for example, two rules can match the same row—adding the results may count that row twice. Design the logic around whether each qualifying row should be counted once or more than once.
Use Wildcards for Partial Text Matches
Criteria can include wildcards when the data contains text. An asterisk (*) matches any number of characters, while a question mark (?) matches one character. A tilde (~) lets you search for a literal asterisk or question mark. Wildcards are useful when product descriptions include extra words, such as “Coffee Beans” or “Ground Coffee.”
With sales in E2:E8, regions in B2:B8, and product descriptions in D2:D8, this sums East sales where the product text contains “Coffee” anywhere:
=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"*Coffee*")
For the small table above, it returns 250 because both East product entries are exactly Coffee. In a larger list, it could also match descriptions such as “Coffee Beans.” A wildcard is a pattern, not a fuzzy search: it will not automatically treat misspellings or unrelated words as equivalent. If you need an exact match, use the plain criterion "Coffee".
Apply Date and Numeric Criteria
Criteria can test dates and numbers as well as text. Operators such as greater than, less than, and not equal are written inside a text criterion. When the comparison value is in a cell, join the operator to the cell reference with &; this ensures Excel builds a criterion from the operator and that cell’s value.
Suppose H2 contains a starting date of Jan 1 and I2 contains an ending date of Jan 9. With dates in A2:A8, East in B2:B8, Coffee in D2:D8, and amounts in E2:E8, this totals matching sales on or after H2 and before the day following I2:
=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Coffee",A2:A8,">="&H2,A2:A8,"<"&I2+1)
It returns 190, from Jan 3 and Jan 9. Using a less-than comparison against the day after the end date is helpful when a date column might contain times as well as dates; it includes all times on the final day. If every value is a date with no time component, an inclusive less-than-or-equal test against the end date is another option.
For a numeric example, assume E2:E8 contains the amounts and F2 contains a threshold of 100. To add amounts above that threshold while also requiring East in B2:B8, use =SUMIFS(E2:E8,B2:B8,"East",E2:E8,">"&F2). It returns 120. Here the amount range is also a criteria range: Excel adds the amount only when it is greater than 100 and the region is East.
Make Criteria Easier to Change
Hard-coded criteria are convenient for a one-off calculation, but cell references make a report easier to reuse. For example, put East in G2 and Coffee in H2. With sales in E2:E8, regions in B2:B8, and products in D2:D8, this formula uses those cells as its criteria:
=SUMIFS(E2:E8,B2:B8,G2,D2:D8,H2)
The result is still 250 when G2 contains East and H2 contains Coffee. Change either cell to recalculate the total without editing the formula. This is useful for summary tables, dropdown-driven reports, and templates where another person may need to select different criteria.
Keep criteria cells consistent with the source data. Extra spaces can make a text value fail to match, and dates stored as text may not behave like real Excel dates. If a criterion refers to a cell containing a number or date, use the reference directly for an equality test. For a comparison such as greater than, join the operator to the reference as in the date example.
Common Errors and How to Fix Them
- Using SUMIF for two conditions: Replace it with
SUMIFSwhen both conditions must be true. Do not try to place multiple independent tests into the one-criterion structure ofSUMIF. - Putting arguments in the wrong order: In
SUMIFS, the sum range comes first. Each criteria range is followed by its matching criterion. - Using ranges of different sizes: Make the sum range and every criteria range cover corresponding rows. For example, do not combine E2:E100 with B2:B99.
- Expecting a match that is not exact: Check for extra spaces, inconsistent spelling, or text values that look like numbers. Wildcards can handle deliberate partial-text matching but will not repair inconsistent data generally.
- Getting zero for dates: Confirm that the source dates and criteria cells are real Excel date values, not text that merely looks like a date. Also check that the date boundaries include the records you expect.
- Using the wrong list separator: The formulas here use commas, as requested for standard English Excel syntax. Some regional settings use semicolons; Excel may display or expect that separator according to the computer’s settings.
When a total looks wrong, inspect a few qualifying rows manually and compare them with the formula’s criteria. Also verify that the sum range contains numeric values. Text numbers may look like amounts but may not be added as expected.
SUMIF, SUMIFS, and Other Options
Use SUMIF when one condition is enough; it remains a clear choice for a single test. Use SUMIFS for two or more conditions that must all match. For a small number of OR alternatives, adding separate SUMIFS calculations is often the most readable solution.
A helper column is another option when the rule is complex or reused. It can label rows that meet a combined test, after which a simpler total can use that label. The trade-off is an extra column to maintain. SUMPRODUCT can also perform multi-condition calculations, but its expressions can be harder for a spreadsheet reader to interpret and are not necessary for ordinary multi-criteria sums.
For most everyday summaries, start with SUMIFS. It expresses the conditions directly, is available in modern Excel versions, and makes it relatively easy to check which criteria are being applied. Choose an alternative when it makes complicated logic more transparent, not merely to avoid the function intended for the task.
Frequently Asked Questions
Can SUMIF have two criteria?
No. SUMIF accepts one criterion. Use SUMIFS when multiple conditions must be satisfied, or combine separate calculations when the logic is OR.
Does SUMIFS use AND or OR logic?
Separate criteria pairs in one SUMIFS call use AND logic: every condition must be met. To express OR, add separate results for the alternatives or structure the calculation so each qualifying row is counted correctly.
Does the sum range go first in SUMIFS?
Yes. The first argument is the sum range. After that, supply a criteria range and its criterion, then any additional range-and-criterion pairs.
Can SUMIFS match part of a word?
Yes. Use wildcards such as *text* for text containing a phrase, or text* for text beginning with it. Use a plain text criterion when the whole cell must match exactly.
Why does my SUMIFS formula return zero?
Check that the ranges align, the criteria match the source values, and dates or numbers are stored in the expected format. Extra spaces, text-formatted dates, and a boundary that excludes a record are common causes.
Can I use cell references for criteria?
Yes. Put the requested text, date, or number in a cell and refer to it as the criterion. For comparisons such as greater than or on or after, combine the operator with the cell reference.
Related
- Excel Refresh Data Shortcut: Refresh All or a PivotTable
- Excel Shortcut to Paste Formatting and Keep Data
- Excel Paste Special Shortcut: Keys and Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Uppercase the First Letter in CellsTo uppercase only the first character in an Excel cell while leaving the rest unchanged, use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)). If…
- Excel Formula for Years Between Dates: Exact or DecimalFor completed years between two dates in Microsoft Excel, enter a start date in A2 and an end…
- Excel Flash Fill: Fill a Column Without a FormulaExcel Flash Fill is not a formula: it recognizes a pattern in examples you type and fills the…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
- How to Find a Middle Name in Excel: Formula and StepsWith full names in A2 and names written in first-middle-last order, enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," "),1,2),"") in B2 to return…
- Excel FIND Function: How to Find Text Within a StringIn Excel, use FIND to locate text inside a string and return the position where it begins. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.