The SUMIFS Excel expression adds numbers that meet multiple conditions. Its basic form is =SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee"): this adds values in C2:C100 only when the corresponding row in column A is East and column B is Lee.
What SUMIFS means and when to use it
SUMIFS is a worksheet function for conditional addition. It is useful when a total should include only records matching one or more rules—for example, sales for a particular region and salesperson, expenses within a date range, or inventory for a selected product category. Without conditions, SUM adds every numeric value in its referenced range. SUMIFS narrows the total to rows that pass its criteria.
The function is available in current Microsoft Excel for Microsoft 365 on Windows, Mac, and the web, and in many earlier desktop versions. Its name can be confusing: SUMIFS handles multiple conditions, while SUMIF handles one condition. SUMIFS can also be used for just one condition; the plural name does not require you to supply several.
SUMIFS syntax and argument order
The argument order is the range to add first, followed by a range to test and the criterion for that range. You can then add more range-and-criterion pairs. In practical terms, the pattern is sum range, criteria range 1, criterion 1, criteria range 2, criterion 2, and so on. The sum range and each criteria range must describe corresponding rows or cells. Excel returns a total rather than a list of matching records.
With amounts in C2:C100, regions in A2:A100, and salespeople in B2:B100, this expression adds amounts for East-region sales handled by Lee:
Assuming the stated layout, use this formula:
=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee")
Text criteria need double quotation marks when typed directly into a formula. A criterion can also refer to a cell, which is helpful for making a report interactive. If F1 contains East and G1 contains Lee, the formula can use those cell references instead of fixed text.
With amounts in C2:C100, regions in A2:A100, and salesperson names in B2:B100, and with the selected region in F1 and name in G1, use:
=SUMIFS(C2:C100,A2:A100,F1,B2:B100,G1)
How SUMIFS applies conditions
SUMIFS tests each row against every criterion pair. A row contributes to the total only if it meets all the conditions: this is an AND relationship. For example, a row must be in the East region and belong to Lee to be included. A row that matches the region but not the salesperson is excluded.
Consider a small table with regions in A2:A6, salespeople in B2:B6, and sales in C2:C6:
- East, Lee, 120
- West, Lee, 90
- East, Maya, 150
- East, Lee, 80
- West, Maya, 110
With that layout, the East-and-Lee total is 200, because 120 plus 80 equals 200. The West-and-Lee row and both Maya rows do not satisfy both conditions. If you change either criterion, Excel recalculates the result against the table.
A common misconception is that adding another condition makes SUMIFS include rows matching either condition. It does the opposite: adding a criterion pair makes the test more restrictive. For an either/or calculation, use separate SUMIFS calculations and add their results, taking care not to count a row twice.
Using comparison operators and cell references
Criteria can test equality, inequality, and numeric thresholds. Put comparison operators inside the quoted criterion when writing them directly, such as greater than or equal to 100. If the threshold is stored in a cell, join the operator to that reference with the ampersand (&). This lets a user adjust the threshold without editing the formula.
With amounts in C2:C6 and regions in A2:A6, the following totals East-region amounts of at least 100. In the example table above, the qualifying values are 120 and 150, so the result is 270:
=SUMIFS(C2:C6,A2:A6,"East",C2:C6,">=100")
With the same amounts in C2:C6, regions in A2:A6, and a minimum amount in F1, use this version to make the cutoff adjustable:
=SUMIFS(C2:C6,A2:A6,"East",C2:C6,">="&F1)
Common operators include greater than (>), less than (<), not equal to (<>), and their equality-inclusive forms (>= and <=). For a blank criterion, SUMIFS can test for an empty cell using an empty text value; for cells that are not blank, use the not-equal operator with an empty text value. Check that the criterion matches the underlying data type: a number stored as text may not behave like a numeric value in comparisons.
Summing dates within a period
Dates are stored as serial numbers in Excel, so date criteria are usually clearest when expressed as a start boundary and an exclusive end boundary. For a month, test dates on or after the first day and before the first day of the following month. This avoids problems with times attached to date values and avoids having to guess the final possible time on the last day.
Assume transaction dates are in A2:A6 and amounts are in C2:C6. If the rows contain January 1 through January 5, 2025, with amounts 100, 200, 150, 250, and 300, the January total is 1,000:
=SUMIFS(C2:C6,A2:A6,">="&DATE(2025,1,1),A2:A6,"<"&DATE(2025,2,1))
DATE constructs real Excel dates from year, month, and day numbers, which is safer than typing a date string whose interpretation may depend on regional settings. For a reusable report, put the start and end dates in worksheet cells and join each comparison operator to its cell reference. Make sure the dates in the data column are actual Excel dates, not text that merely looks like a date.
Text criteria and wildcards
A criterion can match text exactly, subject to the function’s case-insensitive matching. For instance, East matches east in ordinary SUMIFS comparisons. If capitalization matters, SUMIFS alone is not a case-sensitive matching function; a different approach is needed for that requirement.
For partial text matches, Excel supports two wildcards in criteria: an asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) lets you search for a literal wildcard character. For example, a criterion containing a word followed by an asterisk can match longer entries that begin with that word. Wildcards are useful for product names or codes, but may unintentionally match more records than an exact criterion, so inspect the source values when the total is unexpected.
With amounts in C2:C100 and product names in B2:B100, this example totals products whose names begin with the text Paper:
=SUMIFS(C2:C100,B2:B100,"Paper*")
OR conditions and multiple SUMIFS calculations
Because criteria pairs within one SUMIFS are combined with AND, a request such as “East or West” generally needs two SUMIFS calculations. Add the separate totals when the categories cannot overlap. This keeps the intent visible and is often easier to audit than a complicated single expression.
With sales in C2:C100 and regions in A2:A100, this adds all East and West sales. If every row has exactly one region, each record is counted once:
=SUMIFS(C2:C100,A2:A100,"East")+SUMIFS(C2:C100,A2:A100,"West")
If your data allows a record to satisfy both conditions—for example, overlapping text patterns—adding separate results can double-count it. Consider whether the categories are mutually exclusive, or create a clearer classification field in the data before summing.
Range rules and common errors
SUMIFS requires ranges that line up. The sum range and all criteria ranges should have the same dimensions and correspond to the same records. Using C2:C100 for amounts but A2:A99 for a criterion range is a mismatch and can produce an error or an incorrect result. Whole-column references can be convenient, but consistent bounded ranges or Excel Table columns are often easier to inspect in a workbook.
When a formula returns zero or an unexpected total, check these common causes:
- Look for extra spaces or inconsistent spelling in text fields. “East” and “East ” may not match as expected.
- Confirm that numbers and dates are stored as values rather than text. Formatting a text value to look like a number does not convert it.
- Check each criteria range against the sum range, including the first and last row.
- Review quotation marks and operator placement. Direct text criteria need quotes, while a cell reference should normally be used without quotes.
- Check whether the formula should use AND conditions or separate calculations for an OR condition.
When entering a formula, Excel uses commas between arguments in the examples here. Some regional settings use semicolons as the list separator, so Excel may display or require semicolons instead. This is a locale setting difference, not a different SUMIFS calculation. The function is available on Windows, Mac, and Excel for the web; ribbon locations for editing formulas are not needed because the function can be entered directly in a cell or the formula bar.
SUMIFS compared with SUMIF and alternatives
Use SUMIFS when a total must satisfy multiple conditions, or when you want a formula that can readily grow to include another criterion. Use SUMIF for a single-condition total if that form is more readable in a simple worksheet. Both functions are designed for conditional sums, but the argument order differs: SUMIFS starts with the sum range, whereas SUMIF places its criteria range before its optional sum range. This difference is a frequent source of formulas that look plausible but add the wrong cells.
For a straightforward conditional total, SUMIFS is usually easier to maintain than manually filtering and adding visible values. Excel Tables can make formulas more readable by using column names, and filters can help inspect which records meet a condition, but filtering alone does not replace a calculated total. For summaries across several categories, a PivotTable may be more convenient than maintaining many separate formulas. Choose the method based on whether you need a single live total, a repeatable report, or an exploratory summary.
Frequently Asked Questions
What is the basic SUMIFS formula in Excel?
Use SUMIFS with the range to add first, then each criteria range and its criterion. For example, the opening formula adds values in C2:C100 when corresponding rows in A and B meet the two stated text conditions.
Does SUMIFS use AND or OR?
Multiple criteria pairs in one SUMIFS use AND: a row must satisfy every condition. For OR logic, add separate SUMIFS results when doing so will not count any row more than once.
Can SUMIFS work with dates?
Yes. Use a date column as a criteria range and set boundaries with DATE or cells containing real Excel dates. A start-inclusive, next-period-exclusive range is a reliable way to total a month.
Why does my SUMIFS formula return zero?
Check that the criteria really match the source data, that dates and numbers are stored as values, and that all referenced ranges line up. Extra spaces, text-formatted numbers, or an unintended AND condition are common reasons.
Is SUMIFS case-sensitive?
No. Standard SUMIFS text comparisons are not case-sensitive. If capitalization must distinguish otherwise identical text, use a case-sensitive method rather than relying on SUMIFS alone.
Can I use SUMIFS for one condition?
Yes. Supply one criteria range and one criterion. SUMIF is another option for a single condition, but remember that its argument order is different.
Related
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
- XLOOKUP Excel Example: Search Across Two Excel SheetsIf Orders!A2 contains a product code, and Products!A2:A100 contains product codes with prices in Products!C2:C100, enter =XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$C$2:$C$100,"Not found")…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- Excel Data Table Tutorial: One- and Two-Variable What-IfsTo create an Excel Data Table, place a formula and test values in a small grid, select the…
- Excel Date Minus Months: Formulas and ExamplesTo subtract calendar months from a date in Excel, use EDATE with a negative month count. If the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.