Excel SUMIFS Function: Complete Guide with 12 Practical Examples

You need to sum sales for the Northeast region, but only for Q4, and only for products over $100. You could manually filter and calculate, or you could write one SUMIFS formula that does it instantly—and updates automatically when data changes.

SUMIFS is one of Excel's most powerful functions for conditional calculations. Unlike SUMIF (which handles one condition), SUMIFS handles multiple criteria simultaneously, making it essential for business analysis, financial reporting, and data dashboards.

This guide teaches you SUMIFS from basic to advanced with 12 practical examples you'll use immediately.

What is SUMIFS and Why Use It?

SUMIFS = Sum values in a range that meet multiple conditions.

Syntax:

1=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Key advantages:

• Handle multiple conditions (unlimited criteria)
• Dynamic—updates automatically when data changes
• Fast—calculates instantly even with 100,000+ rows
• More powerful than filtering and manual summing
• Essential for dashboards, reports, and analysis

When to use SUMIFS: ✅ Summing sales by region AND product type
✅ Calculating totals within date ranges
✅ Filtering data by multiple categories
✅ Creating dynamic reports and dashboards
✅ Replacing complex pivot tables for simple sums

Real-world impact: A financial analyst using SUMIFS can generate monthly reports in 5 minutes instead of 2 hours of manual filtering and calculation.

SUMIFS vs SUMIF vs SUM: Understanding the Difference

When to use each:

• SUM: Total everything (no conditions)
• SUMIF: One condition (e.g., "sum all sales over $1000")
• SUMIFS: Multiple conditions (e.g., "sum sales over $1000 in Northeast region during Q4")

The SUMIFS Syntax Explained

1=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters breakdown:

1. sum_range (required): The range of cells to sum

   • Example: B2:B100 (sales amounts)

2. criteria_range1 (required): First range to evaluate

   • Example: C2:C100 (regions)

3. criteria1 (required): Condition for criteria_range1

   • Example: "Northeast" or ">100" or A5 (cell reference)

4. criteria_range2, criteria2 (optional): Additional conditions

   • Can add up to 127 condition pairs
   • Each criteria_range must have same size as sum_range

Important notes:

• Ranges must all be the same size
• Text criteria must be in quotes: "Northeast"
• Comparison operators need quotes: ">=100", "<>Pending"
• Cell references don't need quotes: A5
• Wildcards work: "*phone*", "John?", "A??Z"

Example 1: Basic SUMIFS with Two Criteria

Scenario: Sum sales for Product A in the Northeast region.

Data:

Formula:

1=SUMIFS(C:C, A:A, "Product A", B:B, "Northeast")

Result: 1250 (500 + 750)

Explanation:

• Sum_range: C:C (Sales column)
• Criteria_range1: A:A (Product column)
• Criteria1: "Product A"
• Criteria_range2: B:B (Region column)
• Criteria2: "Northeast"

Example 2: Using Cell References as Criteria

Scenario: Make formulas dynamic by referencing criteria cells.

Setup:

• Cell F2: "Product A" (product selector)
• Cell F3: "Northeast" (region selector)

Formula:

1=SUMIFS(C:C, A:A, F2, B:B, F3)

Benefit: Change F2 or F3 to analyze different products/regions instantly without editing formula.

Pro tip: Combine with data validation dropdowns in F2 and F3 for a dynamic dashboard.

Example 3: Date Range Criteria

Scenario: Sum sales between two dates (Q4 2025: Oct 1 to Dec 31).

Data includes Date column (column D):

Formula:

1=SUMIFS(C:C, D:D, ">=10/1/2025", D:D, "<=12/31/2025")

Better approach with cell references:

1=SUMIFS(C:C, D:D, ">="&F2, D:D, "<="&F3)

• F2 contains start date: 10/1/2025
• F3 contains end date: 12/31/2025

Date criteria tips:

• Use quotes for hardcoded dates: ">=1/1/2026"
• Use & to concatenate operators with cell refs: ">="&F2
• Format cells as dates for readability
• Excel understands various date formats (MM/DD/YYYY, DD-MMM-YYYY, etc.)

Example 4: Greater Than / Less Than Criteria

Scenario: Sum sales over $500 in Northeast region.

Formula:

1=SUMIFS(C:C, C:C, ">500", B:B, "Northeast")

Explanation:

• Notice C:C appears twice: once as sum_range, once as criteria_range
• This is valid and common—checking condition on same range you're summing

Common comparison operators:

• ">100" - Greater than 100
• ">=100" - Greater than or equal to 100
• "<100" - Less than 100
• "<=100" - Less than or equal to 100
• "<>100" - Not equal to 100

Example 5: Not Equal To Criteria

Scenario: Sum all sales except those from Southwest region.

Formula:

1=SUMIFS(C:C, B:B, "<>Southwest")

Multi-condition exclusion: Sum sales for all regions except Southwest and Midwest:

1=SUMIFS(C:C, B:B, "<>Southwest") + SUMIFS(C:C, B:B, "<>Midwest")

Wait, that's wrong! It would double-count other regions.

Correct approach: Use SUMIF with wildcards or create helper column, or use this array formula:

1=SUMPRODUCT((B:B<>"Southwest")*(B:B<>"Midwest")*C:C)

Example 6: Wildcard Criteria for Partial Matches

Scenario: Sum sales for all products containing "Phone" (iPhone, Samsung Phone, etc.).

Wildcards:

• * = Any number of characters
• ? = Single character

Formula:

1=SUMIFS(C:C, A:A, "*Phone*")

More examples:

• "A*" - Starts with A
• "*Z" - Ends with Z
• "A??Z" - Starts with A, ends with Z, exactly 2 characters between

Use case: Sum sales for all "Manager" titles:

1=SUMIFS(D:D, C:C, "*Manager*")

Matches: Sales Manager, Regional Manager, Manager of Operations, etc.

Example 7: Combining Multiple Numeric Criteria

Scenario: Sum sales between $500 and $1000 in Northeast region.

Formula:

1=SUMIFS(C:C, C:C, ">=500", C:C, "<=1000", B:B, "Northeast")

Explanation:

• C:C appears THREE times: sum_range, criteria_range1, criteria_range2
• First criteria: >=500
• Second criteria: <=1000
• Third criteria: "Northeast"
• All conditions must be true for row to be included

Example 8: SUMIFS with OR Logic (Multiple Options)

Problem: SUMIFS uses AND logic (all conditions must be true). What if you need OR logic?

Scenario: Sum sales from Northeast OR Northwest regions.

Solution: Use multiple SUMIFS and add them:

1=SUMIFS(C:C, B:B, "Northeast") + SUMIFS(C:C, B:B, "Northwest")

Scenario: Sum Product A sales OR sales over $1000.

Solution: This is trickier—use SUMPRODUCT:

1=SUMPRODUCT((A:A="Product A")+(C:C>1000))*(C:C))

Wait, that won't work correctly either (it would double-count rows meeting both conditions).

Correct OR logic for "Product A OR over $1000":

1=SUMPRODUCT(((A:A="Product A")+(C:C>1000)>0)*C:C)

Pro tip: For complex OR logic, consider helper columns or pivot tables.

Example 9: SUMIFS with Dynamic Column References

Scenario: Sum different months based on user selection.

Setup:

• Columns: A (Product), B (Jan Sales), C (Feb Sales), D (Mar Sales)
• Cell F1: Dropdown with "Jan", "Feb", "Mar"

Formula using INDIRECT:

1=SUMIFS(INDIRECT(F1&" Sales"), A:A, "Product A")

Better approach using INDEX:

1=SUMIFS(INDEX(B:D,,MATCH(F1,{"Jan","Feb","Mar"},0)), A:A, "Product A")

Use case: Dynamic dashboard where users select month from dropdown and all totals update automatically.

Example 10: SUMIFS with MONTH and YEAR Functions

Scenario: Sum sales for specific month and year from date column.

Data: Column D contains dates (1/15/2026, 2/3/2026, etc.)

Sum all January 2026 sales:

1=SUMIFS(C:C, D:D, ">=1/1/2026", D:D, "<2/1/2026")

More flexible approach with SUMPRODUCT:

1=SUMPRODUCT((MONTH(D:D)=1)*(YEAR(D:D)=2026)*C:C)

Dynamic version (month and year in cells F2 and F3):

1=SUMPRODUCT((MONTH(D:D)=F2)*(YEAR(D:D)=F3)*C:C)

Use case: Monthly sales reports where you just update month/year cells instead of rewriting formulas.

Example 11: SUMIFS with Named Ranges

Scenario: Make formulas more readable with named ranges.

Setup:

1. Select C2:C100, name it "Sales"
2. Select A2:A100, name it "Products"
3. Select B2:B100, name it "Regions"

Formula:

1=SUMIFS(Sales, Products, "Product A", Regions, "Northeast")

Benefits:

• Easier to read and understand
• Simpler to audit and debug
• Less error-prone (no column letter mistakes)

Creating named ranges:

• Select range → Formulas tab → Define Name
• Or: Select range, type name in Name Box (left of formula bar), press Enter

Example 12: Error-Proof SUMIFS with IFERROR

Scenario: Handle errors gracefully when criteria ranges are empty or mismatched.

Formula without error handling:

1=SUMIFS(C:C, A:A, F2, B:B, F3)

Problem: Returns #DIV/0! or #VALUE! if F2 or F3 is empty.

Better formula with error handling:

1=IFERROR(SUMIFS(C:C, A:A, F2, B:B, F3), 0)

Result: Returns 0 instead of error when criteria cells are empty.

Even better—check if criteria exist:

1=IF(AND(F2<>"", F3<>""), SUMIFS(C:C, A:A, F2, B:B, F3), "Select criteria")

Use case: Dashboard where users might clear dropdown selections—show friendly message instead of error.

Best Practices for SUMIFS Mastery

1. Use Entire Columns for Scalability

Don't:

1=SUMIFS(C2:C100, A2:A100, "Product A")

Do:

1=SUMIFS(C:C, A:A, "Product A")

Why: Automatically includes new rows added to table. Excel is smart—ignores headers and empty cells.

2. Lock References When Copying Formulas

Use absolute references ($) when copying formulas:

1=SUMIFS($C:$C, $A:$A, F2, $B:$B, G2)

• $C:$C - Column stays fixed when copying right
• F2 - Row changes when copying down (intentional)

3. Use Named Ranges for Complex Formulas

Named ranges make formulas self-documenting:

1=SUMIFS(TotalSales, ProductCategory, SelectedProduct, SalesRegion, SelectedRegion, SaleDate, ">="&StartDate, SaleDate, "<="&EndDate)

4. Combine with Other Functions

SUMIFS with VLOOKUP:

1=SUMIFS(Sales, Products, VLOOKUP(A2, ProductTable, 2, FALSE))

SUMIFS with TEXT for date formatting:

1=SUMIFS(C:C, D:D, TEXT(TODAY(), "mmm yyyy"))

SUMIFS with FILTER (Excel 365):

1=SUM(FILTER(Sales, (Regions="Northeast")*(Products="Product A")))

5. Optimize Performance for Large Datasets

For 100,000+ rows:

• Use structured table references instead of entire columns
• Avoid volatile functions (TODAY, NOW, INDIRECT) in criteria when possible
• Consider Power Query for very large datasets

Example with table reference:

1=SUMIFS(SalesTable[Amount], SalesTable[Product], "Product A")

Common Mistakes and How to Avoid Them

Mistake 1: Mismatched Range Sizes

Error:

1=SUMIFS(C2:C100, A2:A50, "Product A")

Problem: Sum_range (100 rows) and criteria_range (50 rows) don't match.

Fix: Ensure all ranges same size:

1=SUMIFS(C2:C100, A2:A100, "Product A")

Mistake 2: Forgetting Quotes Around Text Criteria

Error:

1=SUMIFS(C:C, A:A, Product A)

Result: #NAME? error

Fix:

1=SUMIFS(C:C, A:A, "Product A")

Mistake 3: Wrong Operator Concatenation

Error:

1=SUMIFS(C:C, D:D, >=&F2)

Fix:

1=SUMIFS(C:C, D:D, ">="&F2)

Rule: Operator must be in quotes, concatenated with & to cell reference.

Mistake 4: Using SUMIFS When SUMIF is Enough

Inefficient:

1=SUMIFS(C:C, A:A, "Product A")

Better (simpler):

1=SUMIF(A:A, "Product A", C:C)

Rule: Use SUMIF for single condition—it's simpler and marginally faster.

Mistake 5: Not Handling Blank Cells

Problem: Criteria like ">0" might include unexpected blanks.

Safer approach:

1=SUMIFS(C:C, C:C, ">0", A:A, "Product A")

This ensures you're only summing actual positive values, not blanks that Excel might treat as zero.

Real-World Applications

Application 1: Sales Dashboard

Create dynamic sales summary:

1Total Sales: =SUMIFS(Sales, Regions, F2, Products, F3, Dates, ">="&F4, Dates, "<="&F5)

Users select region (F2), product (F3), start date (F4), end date (F5)—formula updates instantly.

Application 2: Budget vs Actual Analysis

Sum actual expenses by category and month:

1=SUMIFS(ActualExpenses, Categories, "Marketing", Months, "January")

Compare against budget with variance calculation:

1=Budget_Marketing_Jan - SUMIFS(ActualExpenses, Categories, "Marketing", Months, "January")

Application 3: Inventory Tracking

Calculate inventory value for specific warehouse and product type:

1=SUMIFS(ItemValue, Warehouses, "Warehouse A", ProductTypes, "Electronics", StockStatus, "In Stock")

Application 4: Commission Calculations

Sum sales for commission calculation (only sales over $1000 in specific territories):

1=SUMIFS(Sales, SalesRep, "John Smith", Sales, ">1000", Territory, "Northeast") * 0.05

Advanced Technique: SUMIFS with Array Criteria

Scenario: Sum sales for multiple products at once.

Challenge: SUMIFS doesn't natively support arrays like {"Product A", "Product B"}.

Solution 1: Add multiple SUMIFS:

1=SUMIFS(C:C, A:A, "Product A") + SUMIFS(C:C, A:A, "Product B") + SUMIFS(C:C, A:A, "Product C")

Solution 2: Use SUMPRODUCT with array logic:

1=SUMPRODUCT(((A:A="Product A")+(A:A="Product B")+(A:A="Product C"))*(C:C))

Solution 3: Excel 365 FILTER function:

1=SUM(FILTER(C:C, (A:A="Product A")+(A:A="Product B")+(A:A="Product C")))

SUMIFS vs Pivot Tables: When to Use Each

Rule of thumb: If you need 1-3 specific calculations with defined criteria, use SUMIFS. If you're exploring data or need 10+ different breakdowns, use pivot tables.

Frequently Asked Questions

Can SUMIFS handle more than 127 criteria pairs?

No, Excel limits SUMIFS to 127 condition pairs. If you need more, consider restructuring your data or using helper columns to combine criteria.

Why does SUMIFS return 0 when I know data exists?

Common causes:

1. Extra spaces in text criteria (use TRIM function)
2. Numbers stored as text
3. Date format mismatch
4. Criteria range and sum range different sizes

Can SUMIFS work with different worksheets?

Yes! Syntax:

1=SUMIFS(Sheet2!C:C, Sheet2!A:A, "Product A", Sheet2!B:B, "Northeast")

How do I sum visible cells only (after filtering)?

SUMIFS includes hidden rows. Use SUBTOTAL or AGGREGATE instead:

1=AGGREGATE(9, 5, C:C)

(9=SUM, 5=ignore hidden rows)

Can I use SUMIFS with merged cells?

Not recommended—merged cells break most formulas. Unmerge cells and use proper table structure instead.

What's the difference between SUMIFS and DSUM?

DSUM is older database function with different syntax (criteria in table format). SUMIFS is newer, more flexible, and easier to use. Use SUMIFS unless maintaining legacy workbooks.

Conclusion

SUMIFS is your Swiss Army knife for conditional calculations in Excel. Master it, and you'll:

• Build dynamic dashboards that update automatically
• Eliminate hours of manual filtering and calculating
• Create sophisticated reports without pivot tables
• Answer complex business questions in seconds

Start with basic two-criteria formulas and gradually add complexity. Practice with your own data—the patterns become intuitive quickly.

The 12 examples in this guide cover 90% of real-world SUMIFS use cases. Bookmark this page, reference it when building formulas, and soon you'll be writing complex SUMIFS from memory.

Next step: Apply SUMIFS to your actual work. Pick one report you currently do manually and recreate it with SUMIFS formulas. Watch how much time you save.

Related articles: Seven Excel Formulas Every Professional Should Know, Excel XLOOKUP Complete Guide with Examples