SUMIFS in Excel adds values only when they meet one or more conditions. For example, with sales in D2:D8 and regions in A2:A8, use =SUMIFS(D2:D8,A2:A8,"East") to total sales for the East region.
What SUMIFS does
SUMIFS is useful when a total depends on more than a single category. You might want sales for one region and one representative, expenses for a particular department during a month, or inventory quantities for products below a chosen threshold. The function checks each row against the conditions you specify and adds the corresponding values from the sum range only when all conditions match.
That last point matters: conditions within one SUMIFS are joined with AND logic. A row must meet every condition. If you ask for region East and representative Ana, a sale in East by Ben does not count, nor does a sale by Ana in West.
SUMIFS is available in current desktop and web versions of Excel, as well as older editions beginning with Excel 2007. It is a standard worksheet function, so you can enter it directly in a cell or use it as part of a larger formula.
SUMIFS syntax and argument order
The function takes a sum range followed by pairs of criteria ranges and criteria. The sum range contains the numbers to add. Each criteria range contains the values Excel should inspect, and its accompanying criterion tells Excel what to look for. Add another range-and-criterion pair for each additional condition.
For example, the first range-and-criterion pair can check a region, and a second pair can check a salesperson. The corresponding ranges must line up row by row with the sum range. If the sum range covers rows 2 through 8, each criteria range should normally cover those same rows.
SUMIFS and SUMIF have different argument orders. SUMIFS puts the sum range first; SUMIF puts the sum range after its first criteria range and criterion. This is a frequent source of errors when switching between the functions. For a formula with only one condition, SUMIFS still works and uses the same argument order as it does with several conditions.
A basic SUMIFS example
Suppose a worksheet has Region in A2:A8, Salesperson in B2:B8, Product in C2:C8, and Sales in D2:D8. The seven sales amounts are 120, 200, 150, 80, 90, 60, and 110. With those ranges, enter =SUMIFS(D2:D8,A2:A8,"East") to add the sales whose region is East. If the East rows contain 120, 200, 80, and 60, the result is 460.
To narrow the result to one salesperson, use the same sales and region ranges and add the salesperson range and criterion: =SUMIFS(D2:D8,A2:A8,"East",B2:B8,"Ana"). In this example, Ana’s East sales are 120, 80, and 60, so the result is 260. The formula does not add Ana’s West sales or East sales made by someone else.
As you build a formula, it helps to identify each range before typing. Confirm which column holds the numbers to add, then pair each condition with the column that contains the values Excel should check. This makes long formulas easier to read and reduces the chance of pairing a criterion with the wrong column.
Using multiple criteria and comparison operators
Criteria can be exact text, a number, a cell reference, or a comparison such as greater than or equal to a value. In the example table, Product is in C2:C8 and Sales is in D2:D8. To total Hardware sales of at least 100, use =SUMIFS(D2:D8,C2:C8,"Hardware",D2:D8,">=100"). Hardware sales that qualify are 120 and 150, so the result is 270. Here the sales column serves both as the sum range and as a criteria range.
When an operator is combined with a value stored in a cell, join the operator to the cell reference with an ampersand. For instance, a criterion can be built from the greater-than-or-equal operator and a threshold in F1. This lets a user change the threshold without editing the formula. Text criteria and comparison operators should be enclosed in double quotation marks; a cell reference by itself does not need quotation marks.
Common comparison operators include > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to). For example, a criterion of "<>East" means any value other than East. These operators work with numeric and date criteria as well as text where the comparison is meaningful.
Wildcards and text matching
For text criteria, Excel supports two common wildcards. An asterisk (*) represents any number of characters, including none; a question mark (?) represents one character. If the salesperson names are in B2:B8 and sales are in D2:D8, =SUMIFS(D2:D8,B2:B8,"A*") totals rows where the name starts with A. In the example, that is Ana’s sales across both regions: 520.
Wildcards are useful when labels are inconsistent or when you want to match a pattern rather than a complete text value. For example, "*hardware*" can match text containing the word hardware, while "B?" matches two-character values beginning with B. Excel does not distinguish uppercase from lowercase in ordinary SUMIFS text matching. To match a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde, such as "~*".
Summing values within a date range
Dates in Excel are stored as serial numbers, so SUMIFS can compare them using the same greater-than and less-than operators. Using a half-open interval—on or after the first day of a period, but before the first day of the next period—is a reliable way to include every date in a month, even if the source cells include times.
Suppose transaction dates are in A2:A5 and amounts are in B2:B5. The rows contain January 1, 2026 ($100), January 15 ($150), February 1 ($200), and January 31 ($50). To total January, enter =SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1)). The January entries total 100 + 150 + 50, or 300. DATE constructs real date values rather than relying on text that may be interpreted differently under another regional date format.
If a start date is in F1 and the next period’s start date is in G1, the same pattern can use those cell references instead of fixed dates. Make sure the source dates are actual Excel dates, not text that merely looks like a date; text dates may not compare as expected.
OR conditions and SUMIFS alternatives
Because multiple conditions in one SUMIFS are combined with AND, a formula asking for East and West at the same time will not return an either-region total. For a simple OR across distinct categories, add separate SUMIFS results. With regions in A2:A8 and sales in D2:D8, one way to total East or West is =SUMIFS(D2:D8,A2:A8,"East")+SUMIFS(D2:D8,A2:A8,"West"). This works when each sale belongs to only one of those regions. If categories can overlap, adding separate totals may count a row twice.
SUMIF is a reasonable choice when a task has only one criterion, but SUMIFS is often easier to extend because it keeps the sum range first and supports additional condition pairs. For more complex logic, such as combinations of AND and OR conditions across several groups, a helper column or a PivotTable may be easier to audit than one long formula. The best choice depends on whether the calculation needs to update automatically with cell values and how easy the result must be for another person to inspect.
Common SUMIFS problems to check
- Ranges have different sizes. Keep the sum range and criteria ranges aligned and the same shape. For example, do not pair D2:D8 with A2:A9.
- The arguments are in the wrong order. SUMIFS starts with the sum range, then takes each criteria range and criterion as a pair.
- A comparison operator is missing from the criteria string. Text such as
">=100"is a criterion; entering just 100 would look for an exact match instead. - Numbers or dates are stored as text. A cell that looks numeric may not behave like a number. Check the source data and convert text values when needed.
- The formula uses the wrong separator for the locale. The formulas here use commas, as requested for standard English-language Excel syntax. Some regional Excel settings use semicolons between arguments; use the separator expected by your installation.
- The formula returns zero unexpectedly. Check for extra spaces, inconsistent labels, dates outside the intended boundaries, and criteria that do not exactly match the data. Also verify that the values in the sum range are numeric.
When auditing a complicated result, test one condition at a time and compare the result with a small filtered sample of the source rows. Once the single-condition total is correct, add the next criteria pair. This makes it easier to locate a mismatch than troubleshooting several criteria simultaneously.
Working with Excel tables
Converting a data range into an Excel table can make SUMIFS formulas easier to maintain. Tables expand when new rows are added, and their column names can make the formula’s purpose more apparent than cell addresses. To create one in Excel for Microsoft 365 on Windows, select a cell in the data and choose Insert > Table, then confirm whether the table has headers.
After creating a table, a formula can refer to its named columns rather than fixed ranges. For example, if the table is named SalesData and its columns are Amount and Region, a structured-reference SUMIFS can use those column names. Check the table and column names in your workbook when adapting such a formula; names must match exactly. Structured references usually adjust as table rows are added, which can help prevent a total from overlooking newly appended records.
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF is designed for one condition and places its sum range after the criterion arguments. SUMIFS begins with the sum range and can apply multiple condition pairs. SUMIFS also works for a single condition.
Can SUMIFS use more than one condition?
Yes. Add a criteria-range and criterion pair for each condition. Excel adds a row only when it satisfies every condition in that SUMIFS formula.
Does SUMIFS support wildcards?
Yes. Use * for any number of characters and ? for one character in text criteria. Use a tilde to treat a wildcard character literally.
Why does my SUMIFS formula return zero?
Often no row satisfies all the criteria exactly, or a range, date, number, or text label is not in the expected form. Confirm that the ranges align, the criteria match the source data, and the sum cells contain numbers.
Can SUMIFS total a date period?
Yes. Use a lower-bound criterion for the period’s first date and an exclusive upper bound for the next period’s first date. This includes the entire intended period and avoids accidentally excluding dates that contain times.
Related
- Excel Data Model: What It Is and How to Use It
- Excel Data Model: Can You Turn It Off?
- Excel Data Model and SharePoint: How They Work
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…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Text Split in Excel: Methods, Formulas, and Common FixesTo split text in Excel, select the cells and choose Data > Text to Columns to separate values…
- Nested XLOOKUP Examples for ExcelA nested XLOOKUP places one XLOOKUP inside another, so one search can supply the lookup value, return array,…
- Concat Excel Date Format: Keep Dates ReadableTo concatenate a date in Excel and keep it readable, convert the date to formatted text with TEXT…
- Excel MIN Without Blanks: What the Formula IgnoresIf your values are in A2:A10, use =MIN(A2:A10) to find the smallest number while ignoring blank cells. Excel’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.