To sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in B2:B8, products in C2:C8, and sales in D2:D8, =SUMIFS(D2:D8,B2:B8,"East",C2:C8,"Widget") returns 370: it adds sales where the region is East and the product is Widget.
What SUMIFS does
SUMIFS adds numbers from a sum range only when the corresponding entries meet every condition you specify. That makes it useful for questions such as “How much did the East region sell of one product?” or “What were total sales for one representative during a date range?” It is available in current Microsoft Excel for Microsoft 365 and in older desktop versions, including Excel 2007 and later.
The key detail is that multiple criteria in one SUMIFS are joined with AND logic. A row must satisfy criterion one, criterion two, and every other criterion in the formula to be included. Excel checks each row’s corresponding cells; it does not search each range independently and combine unrelated matches.
Use this sample layout for the examples below: column A contains dates, B contains region, C contains product, and D contains sales. Rows 2 through 8 hold these entries: Jan 5, East, Widget, 120; Jan 12, East, Gadget, 80; Jan 20, West, Widget, 150; Feb 2, East, Widget, 200; Feb 10, East, Widget, 50; Feb 15, West, Gadget, 90; and Mar 1, East, Gadget, 70. The first formula returns 370 because the qualifying East Widget sales are 120, 200, and 50.
How to build a multiple-criteria formula
Start with the numbers you want to add, then pair each condition with the column or range Excel should check. In the sample, sales are in D2:D8, region is in B2:B8, and product is in C2:C8. The sum range comes first; each criteria range is followed immediately by its criterion.
- Choose the sum range: the cells containing the values to total.
- Choose a criteria range for each condition, such as the region and product columns.
- Enter a criterion as text in quotation marks, a number, a cell reference, or a comparison expression.
- Check that all ranges cover the same rows, then verify the result against a few qualifying records.
With region in B2:B8, product in C2:C8, and sales in D2:D8, this formula sums East-region Widget sales: =SUMIFS(D2:D8,B2:B8,"East",C2:C8,"Widget"). The result is 370. The order matters: unlike some functions, SUMIFS puts the sum range first, before its criteria-range and criterion pairs.
SUMIFS syntax and criteria order
The general structure is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The square brackets in this description indicate optional arguments; do not type them into the formula. You can add more range-and-criterion pairs when the task has more conditions, such as region, product, salesperson, and date. Keep each pair together so it is clear which criterion applies to which column.
For instance, you can put the criteria in worksheet cells rather than typing them into the formula. Assume F2 contains East, F3 contains Widget, and the source data remains in B2:B8, C2:C8, and D2:D8. The formula is =SUMIFS(D2:D8,B2:B8,F2,C2:C8,F3), and it returns 370 with those cell values. This approach makes a report easier to reuse: change the entries in F2 or F3 to recalculate the total without editing the formula.
For a formula copied to other locations, consider whether references should move. A reference such as D2:D8 changes when copied; $D$2:$D$8 stays fixed. Press F4 after selecting a reference in the formula bar to cycle through reference styles on Windows. On Mac, the shortcut may depend on keyboard settings; use the formula bar to enter dollar signs directly if needed.
Text, numbers, comparisons, and dates
Criteria can test exact text, values, or comparisons. Text criteria such as East need quotation marks when typed directly into a formula. For a greater-than test, include the comparison operator and value together as text. With sales in D2:D8, the formula =SUMIFS(D2:D8,D2:D8,">100") returns 470, adding the three values greater than 100: 120, 150, and 200. A criterion such as ">=100" would also include values equal to 100.
To build a comparison from a cell, join the operator to the cell reference with the ampersand. Assume F4 contains 100 and sales are still in D2:D8. The formula =SUMIFS(D2:D8,D2:D8,">"&F4) returns 470. This is handy for adjustable thresholds. If F4 changes to 150, Excel recalculates using the new cutoff.
Dates work best when Excel recognizes the cells as actual dates, rather than text that merely looks like a date. To total January 2026 sales using dates in A2:A8 and amounts in D2:D8, use =SUMIFS(D2:D8,A2:A8,">="&DATE(2026,1,1),A2:A8,"<"&DATE(2026,2,1)). The result is 350. Using a start date inclusive and the next month’s start as an exclusive limit includes every January date, including any entries with a time component. The DATE function also avoids ambiguity between regional date formats.
For partial text matches, use wildcards: an asterisk represents any number of characters, and a question mark represents one character. The tilde escapes a wildcard when you need to match it literally. With product names in C2:C8 and sales in D2:D8, =SUMIFS(D2:D8,C2:C8,"*Widget*") returns 520, the total for entries whose product text contains Widget. Exact text comparisons are not case-sensitive, so this function does not distinguish Widget from WIDGET.
How to handle OR conditions
Multiple criteria inside one SUMIFS mean AND, not OR. If you want East or West, a straightforward method is to add two separate totals. With regions in B2:B8 and sales in D2:D8, =SUMIFS(D2:D8,B2:B8,"East")+SUMIFS(D2:D8,B2:B8,"West") returns 760, the sum of all seven sample sales. This is readable and works in older Excel versions as well as Microsoft 365.
Adding separate totals is safe when the alternatives are mutually exclusive, as East and West are in this example. If the conditions overlap, a row may be counted twice. For example, summing all products named Widget and all sales above 100 would count an East Widget sale above 100 in both totals. Before using an addition approach, decide whether duplicates belong in the answer; if not, construct criteria or a calculation that counts each matching row once.
SUMIFS compared with SUMIF and other options
Use SUMIF for one condition and SUMIFS for multiple conditions. The argument order differs: SUMIF takes the criteria range before the sum range, while SUMIFS starts with the sum range. This reversal is a common source of errors when converting an existing formula. When a task has only one condition, either function may work, but keeping a report consistent can make it easier to maintain.
A PivotTable is often better when you want to explore totals by several categories, compare groups, or rearrange a report interactively. For a fixed total in a worksheet cell, SUMIFS is usually simpler and updates as source values change. More complex formulas, such as SUMPRODUCT, can handle some calculations that are awkward to express with SUMIFS, but they may be harder for someone else to audit. Start with SUMIFS when its criteria model fits the question.
Common problems and how to fix them
- Ranges have different sizes. The sum range and criteria ranges should align row by row and cover the same number of cells. For example, using D2:D8 with B2:B9 is a mismatch. Correct the endpoints or use consistently sized table columns.
- Text or spaces do not match. A value such as East followed by a trailing space may not match the criterion East. Check imported data for extra spaces, inconsistent labels, or nonprinting characters. Wildcards can help with intentional partial matches, but they can also include records you did not mean to include.
- Dates are stored as text. A displayed date may not be a real Excel date value. If date criteria return an unexpected total, inspect the source cells and convert text dates to real dates before relying on the calculation.
- The formula returns zero. Confirm that the criteria actually occur in the chosen rows, that text is spelled consistently, and that the sum range contains numeric values. Also check whether a criterion reference points to the intended cell.
- Relative references shift when copied. Use absolute references such as
$B$2:$B$8for fixed source ranges. If you are using an Excel table, structured references can expand as rows are added and are often easier to read in a growing dataset.
One practical check is to filter the source table to the same conditions and compare the visible records with the formula’s total. This can reveal a wrong range, a misspelled criterion, or a value stored as text. If a range includes a header row, decide deliberately whether it should be excluded; normally the sum and criteria ranges should begin at the first data row.
Make formulas easier to maintain
For recurring reports, convert the data range to an Excel table using Insert > Table, or press Ctrl+T on Windows. On Mac, use Command+T when the data range is selected, or choose Insert > Table. Tables provide named columns and expand when new rows are added, reducing the chance that a formula omits newly entered records. Check the table’s header names and use them consistently in formulas.
Keep criteria cells visible and label them clearly, such as Region and Product. That makes a formula easier to review than a long expression with several values embedded in it. If you need a total by month, use real date boundaries rather than matching month names stored as text; this preserves the year distinction and handles dates reliably. A small, well-labeled criteria area can turn a one-off calculation into a useful, reusable summary.
Frequently Asked Questions
Can SUMIFS use more than two criteria?
Yes. Add another criteria-range and criterion pair for each condition. Every pair must align with the same rows as the sum range, and all conditions in the formula must be true for a row to contribute.
Does SUMIFS use AND or OR?
It uses AND between criteria pairs: a row must meet all of them. To express OR, add separate SUMIFS results when the alternatives do not overlap, or use a method that avoids counting overlapping matches twice.
Can SUMIFS sum between two dates?
Yes. Use the date column twice: once for a start-date condition and once for an end-date condition. An inclusive start and exclusive next-period boundary is a reliable pattern, especially if date cells might contain times.
Why does SUMIFS return zero when matching rows exist?
Check whether criteria text has extra spaces or different spelling, whether dates are genuine Excel dates, and whether the ranges cover the intended rows. Also confirm that the values in the sum range are numeric rather than text.
Is SUMIFS case-sensitive?
No. Standard text criteria in SUMIFS are not case-sensitive. If capitalization must distinguish otherwise identical text, the formula needs a different approach rather than ordinary SUMIFS criteria.
Related
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Add Single Quotes in an Excel Formula CorrectlyIf A2 contains North and you want the result to display as ‘North’, enter this formula in B2:…
- Excel SEQUENCE Examples for Numbers, Dates, and MoreExcel’s SEQUENCE function creates a spill range of consecutive numbers or other values: enter =SEQUENCE(5) in a blank…
- Excel Column Letter to Number: Formula, Examples, and TipsTo convert an Excel column letter to its number, put the letter in A1 and enter =COLUMN(INDIRECT(UPPER(A1)&"1")) in…
- Excel How to Filter Columns: Steps and TipsTo filter columns in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.