Excel SUMIFS Function: Syntax, Examples, and Common Errors (Step-by-Step)

Excel SUMIFS is the function you reach for when you need to sum values based on multiple conditions — sum sales for the West region in Q3, count hours worked by employees in a specific department within a date range, total invoices from a specific customer in a specific month. SUMIF (no S) handles a single condition. SUMIFS handles multiple. Once you understand the syntax, it becomes one of the most useful functions in Excel.

The syntax is straightforward but the argument order trips people up: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The sum_range comes first, then pairs of criteria_range and criteria for each condition. Note this is the opposite of SUMIF, where the criteria range comes first and the sum range comes last (or is omitted). Mix them up and the formula returns wrong values or errors.

A basic SUMIFS example: =SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">1000"). This sums values in column C where column A equals "West" AND column B is greater than 1000. The conditions combine with AND logic — all conditions must be true for a row's value to be included in the sum. There's no OR logic in SUMIFS itself; you'd need SUMPRODUCT or multiple SUMIFS added together for OR conditions.

The criteria argument has flexible syntax. Direct comparisons (=, <, >, <=, >=, <>) work with numbers and text. Wildcards (* for any characters, ? for single character) work with text criteria — "*West*" matches anything containing "West". Cell references can be used: instead of "West" hard-coded, use D1 if D1 contains the region name you want to match. Date criteria work with date values — ">="&DATE(2026,1,1) sums from January 1, 2026 onward.

SUMIFS is significantly faster than the alternative approaches for multi-condition sums. SUMPRODUCT with multiple Boolean conditions, array formulas with IF+SUM, and PivotTables all accomplish similar goals but each has trade-offs. SUMPRODUCT is more flexible (supports OR logic and array operations) but slower on large data sets. PivotTables are interactive and better for exploratory analysis but worse for fixed report formulas. SUMIFS hits the sweet spot for static reports where you need fast, recalculating multi-condition sums.

This guide walks through the SUMIFS syntax in detail, common patterns (date ranges, wildcards, between-values), the most frequent errors and how to fix them, and when to use SUMIFS vs. SUMPRODUCT vs. PivotTables. It's intended for both Excel beginners learning the function and intermediate users who want to use it more effectively.

Let's walk through SUMIFS with a concrete dataset. Imagine a sales table with columns: Date (A), Region (B), Salesperson (C), Product (D), Amount (E). Rows 2-100 contain the data. We want to answer specific questions using SUMIFS.

Question 1: Total sales for the West region. Formula: =SUMIFS(E2:E100, B2:B100, "West"). The sum range is E2:E100 (amounts), the criteria range is B2:B100 (regions), and the criteria is "West". This is equivalent to SUMIF in this single-criterion case.

Question 2: Total West region sales in Q1 2026. Formula: =SUMIFS(E2:E100, B2:B100, "West", A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,4,1)). Three criteria: region = West, date >= Jan 1 2026, date < Apr 1 2026. Note the use of DATE() function inside the criteria to construct date comparisons.

Question 3: Total sales for salesperson Jane in the West region. Formula: =SUMIFS(E2:E100, B2:B100, "West", C2:C100, "Jane"). Two criteria with AND logic — both must be true for the row to be included.

Question 4: Total sales for products containing "Widget" anywhere in the name. Formula: =SUMIFS(E2:E100, D2:D100, "*Widget*"). The asterisks are wildcards that match any text before or after "Widget". "Mega Widget Pro" would match; "Widget" alone would match; "Widgetron" would match.

Question 5: Total sales between $100 and $1000. Formula: =SUMIFS(E2:E100, E2:E100, ">=100", E2:E100, "<=1000"). Same range used as both sum_range AND a criteria_range — perfectly valid. The criteria are >=100 AND <=1000, both checked against the amount column.

Question 6: Total sales NOT in the East region. Formula: =SUMIFS(E2:E100, B2:B100, "<>East"). The <> operator means "not equal to". This sums everything where Region is anything other than East.

Common SUMIFS errors and how to fix them. The most frequent error is #VALUE! which usually means the criteria ranges have different sizes. SUMIFS requires that all criteria ranges and the sum range have the same number of rows. If sum_range is B2:B100 (99 rows) and criteria_range1 is C2:C50 (49 rows), Excel returns #VALUE! because they don't align. The fix is to ensure all ranges have identical dimensions.

The second most common error is silent — the formula returns 0 (or a wrong value) without raising an error. This usually means the criteria text doesn't match the actual data. Common causes: trailing spaces in cell data, case mismatches (SUMIFS is case-insensitive for text but specific characters matter), text-vs-number formatting (criteria "100" looks for text "100"; criteria 100 looks for number 100), and curly quotes vs straight quotes when copying criteria from Word documents.

To debug silent zero-return SUMIFS, isolate the criteria one at a time. Make the formula =SUMIFS(E2:E100, B2:B100, "West") and check if it returns a reasonable number. If it returns 0, the issue is with that criterion. If it returns a number, add the next criterion and check again. This step-by-step approach finds which criterion is failing to match.

The #DIV/0! error in SUMIFS context usually appears when you're using SUMIFS as part of a division formula like =SUMIFS(...) / COUNTIFS(...) and COUNTIFS returns zero. The fix is to wrap the division in IFERROR or to check the COUNTIFS result first: =IF(COUNTIFS(...)>0, SUMIFS(...)/COUNTIFS(...), 0).

Performance gotcha: SUMIFS on entire columns (B:B instead of B2:B100) is fine on small workbooks but slow on workbooks with hundreds of thousands of rows. Modern Excel handles whole-column references better than older versions did, but specifying actual ranges (B2:B100) is still faster on large datasets. If your workbook is slow, check whether SUMIFS formulas reference whole columns unnecessarily.

SUMIFS vs. SUMIF: when to use which? Use SUMIF for a single condition where the syntax is simpler. SUMIF syntax: =SUMIF(criteria_range, criteria, [sum_range]). Note the argument order is different — criteria_range first, then criteria, then sum_range last (and optional, if you're summing the criteria range itself). Use SUMIFS for multiple conditions, but also use it for single conditions if you're learning Excel now — the SUMIFS argument order is more consistent with SUMPRODUCT and other modern functions.

SUMIFS with OR logic requires creative approaches because the function itself only does AND. The three common ways to handle OR conditions:

Approach 1: Multiple SUMIFS added together. =SUMIFS(E:E, B:B, "West") + SUMIFS(E:E, B:B, "East"). This works for OR between criteria on the same column. Simple but verbose with many OR conditions.

Approach 2: SUMIFS with an array of criteria. =SUM(SUMIFS(E:E, B:B, {"West","East"})). The array of criteria values produces an array result that SUM totals. This is cleaner than multiple SUMIFS and works well for OR conditions on a single column.

Approach 3: SUMPRODUCT for complex OR logic. =SUMPRODUCT((B2:B100="West")+(B2:B100="East"))*(C2:C100="Jane")*E2:E100. The Boolean arrays evaluate to 1 or 0; the products and sums give the final result. More flexible than SUMIFS but slower on large data.

For numeric ranges with OR-like conditions, you can sometimes use math instead of OR. To sum amounts where region is West OR amount is over $1000: =SUMIFS(E:E, B:B, "West") + SUMIFS(E:E, E:E, ">1000") - SUMIFS(E:E, B:B, "West", E:E, ">1000"). The subtraction prevents double-counting rows that match both conditions.

SUMIFS works well in combination with other functions. =SUMIFS(E:E, A:A, ">="&EOMONTH(TODAY(),-1)+1, A:A, "<="&EOMONTH(TODAY(),0)) sums values from the current month using EOMONTH to determine month boundaries. =SUMIFS(E:E, B:B, INDEX(F:F, MATCH(...))) lets you look up a criterion dynamically. These compositions get powerful quickly.

SUMIFS vs PivotTables — when to use which? PivotTables are better for exploratory analysis where you don't know the exact questions you'll ask. Drop the data into a PivotTable, drag fields around to slice and filter, and answer different questions in seconds. SUMIFS formulas are better for fixed reports where you know exactly what numbers you want and where they should appear. Once you've built the SUMIFS formulas, they update automatically when source data changes — no PivotTable refresh needed.

The decision rule: if the report layout is fixed and the data is the only thing changing, SUMIFS is usually cleaner. If you're iterating on what questions to ask, PivotTables are faster. Many production workbooks use both — PivotTables for analysis screens, SUMIFS for the summary calculations that downstream formulas depend on.

SUMIFS with Excel Tables (Ctrl+T to create) makes the formulas more readable. Structured references like =SUMIFS(Sales[Amount], Sales[Region], "West") replace cell-range references with table-and-column names. The formula is self-documenting and updates automatically when you add rows to the table. This is the preferred approach for spreadsheets you'll maintain over time.

Excel SUMIFS works in all modern Excel versions — Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, and older. The function was introduced in Excel 2007 to replace the more awkward array-formula approaches used in earlier versions. If you're seeing SUMIFS in a legacy workbook that doesn't seem to work, the issue might be Excel version compatibility — workbooks saved in newer formats sometimes have function calls that older Excel doesn't recognize.

Google Sheets has a SUMIFS function with similar syntax. The argument order and behavior match Excel closely, with minor differences in date handling and wildcard behavior. Formulas usually port directly between the two systems with minor adjustments. The major exception is Excel's newer dynamic array functions (SORT, FILTER, UNIQUE) which Google Sheets has only partially adopted; SUMIFS is fully compatible.

Common real-world SUMIFS use cases and the formula patterns for each:

Use case 1: Monthly sales totals. =SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(2026,5,1), Sales[Date], "<"&DATE(2026,6,1)) sums May 2026. Build a small table with one row per month, point each row's SUMIFS at the appropriate month boundaries, and you have a monthly summary that updates automatically as the Sales table grows.

Use case 2: Per-customer outstanding receivables. =SUMIFS(Invoices[Amount], Invoices[Customer], A2, Invoices[Status], "Unpaid") where A2 contains a customer name. Drag down through a list of customers to get per-customer outstanding totals.

Use case 3: Department budget vs actuals. =SUMIFS(Expenses[Amount], Expenses[Department], B2, Expenses[Year], 2026) returns total 2026 expenses for the department in B2. Compare to budgeted amount in another column for variance analysis.

Use case 4: Filtering for specific products and date range. =SUMIFS(Sales[Amount], Sales[Product], "*Pro*", Sales[Date], ">="&G1, Sales[Date], "<="&G2) sums sales of any product containing "Pro" between dates in G1 and G2. Powerful for product analysis with dynamic date filters.

Use case 5: Inventory count with exclusions. =SUMIFS(Inventory[Quantity], Inventory[Status], "<>Discontinued", Inventory[Warehouse], A2) counts current inventory excluding discontinued items, per warehouse.

For more on excel function technique generally, see excel sumifs related resources. The broader excel sumifs skill set includes pivot tables, lookup functions (VLOOKUP, XLOOKUP, INDEX/MATCH), and conditional formatting — each complements SUMIFS in different ways.

SUMIFS is the workhorse function for multi-condition aggregations in Excel. Once you internalize the argument order — sum range first, then criteria pairs — the function becomes second nature. The patterns covered in this guide (single criterion, multiple AND, date ranges, wildcards, between values, NOT logic) cover the vast majority of practical SUMIFS use cases.

For the more complex situations — OR logic, mixed array operations, deeply nested conditions — SUMPRODUCT remains the more flexible tool, at the cost of slower performance. For exploratory data analysis, PivotTables remain faster than building SUMIFS formulas. But for fixed reports, dashboards, and recurring calculations, SUMIFS is the right default. Master it once and you'll use it weekly for years.

A few additional practical tips worth knowing as you build SUMIFS formulas regularly. First, document complex SUMIFS formulas with a cell comment or a Name Manager defined name. A formula like =SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Date], ">="&EOMONTH(TODAY(),-3)+1, Sales[Date], "<="&EOMONTH(TODAY(),0)) is hard to read months later when you need to update it. A defined name like LastQuarterWestSales pointing at the formula makes both the formula bar and dependent formulas much more readable.

Second, when using SUMIFS in dashboards that recalculate frequently, consider whether you need exact precision or approximate is fine. Volatile functions like TODAY() and NOW() inside SUMIFS criteria force a recalculation on every workbook change, which can slow large dashboards. If "as of this morning" precision is fine, replace TODAY() with a manually-updated cell that you change once daily.

Third, test your SUMIFS results against an alternative method when first building a formula. Sum the same conditions via PivotTable or manually-filtered subtotal, and verify the SUMIFS returns the same value. This catches the silent zero-return errors caused by trailing spaces or case mismatches before they affect downstream calculations.

Fourth, SUMIFS pairs naturally with COUNTIFS for per-group analysis. =SUMIFS(Sales[Amount], Sales[Region], "West") returns the total; =COUNTIFS(Sales[Region], "West") returns the count of transactions; dividing one by the other gives you the average sale size in the West. The two functions use identical criteria syntax, so once you know one, you know the other.

Fifth, when migrating Excel workbooks across versions or to Google Sheets, the SUMIFS function transfers cleanly in almost all cases. Compatibility issues are rare. The exceptions are when SUMIFS is combined with newer dynamic array functions (FILTER, UNIQUE, SORT in Microsoft 365) that older Excel versions or Google Sheets don't fully support. Plain SUMIFS itself works everywhere.

Finally, on learning curve: most users become productive with SUMIFS within a single afternoon of focused practice. The function is conceptually simple — sum these cells, but only where these conditions are met — and once you stop confusing it with SUMIF's argument order, it becomes reliable. The first 5-10 formulas are slow to write; by the 20th you're typing them without thinking. This is one of the highest-leverage Excel functions to learn well, and the time investment pays back quickly.

One closing comparison worth making: SUMIFS versus the newer dynamic array approach in Microsoft 365 Excel. Functions like FILTER combined with SUM produce similar results — =SUM(FILTER(Sales[Amount], (Sales[Region]="West")*(Sales[Date]>=DATE(2026,1,1)))) returns the same value as the equivalent SUMIFS formula. The FILTER approach is more flexible for complex conditions but slightly less performant on large data.

For pure multi-condition sums where you want maximum speed, SUMIFS still wins. For situations where you want to return the filtered rows themselves (not just the sum), FILTER is the right tool. The two functions complement each other rather than compete; most modern Excel workbooks use both for different purposes depending on whether the goal is aggregation or selection.