SUMIFS Function: Sum with Multiple Criteria (2025)
Quick Answer: SUMIFS sums with multiple criteria: =SUMIFS(sum_range, range1, criteria1, range2, criteria2). Example: =SUMIFS(D:D, A:A, "Apple", C:C, ">100") sums D where A=Apple AND C>100. Use AND logic only.
What is SUMIFS?
SUMIFS sums cells that meet multiple conditions using AND logic. All criteria must be true for a value to be included in the sum. Perfect for multi-dimensional data analysis.
Simple Example:
=SUMIFS(D2:D100, A2:A100, "Apple", C2:C100, ">100") // Sum values in D where: // - Column A = "Apple" AND // - Column C > 100
SUMIFS (Multiple Criteria)
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
AND logic - all conditions must match
SUMIF (Single Criteria)
=SUMIF(range, criteria, sum_range)
One condition only - simpler syntax
⚠️ Key Difference from SUMIF
SUMIF: =SUMIF(criteria_range, criteria, sum_range)
SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1, ...)
⚠️ Sum range comes FIRST in SUMIFS, LAST in SUMIF!
💡 Common Use Cases
- • Sales analysis: Sum revenue by region AND product category
- • Inventory: Sum quantities for specific supplier AND warehouse
- • Financial reports: Sum expenses by department AND date range
- • Performance tracking: Sum sales where rep = "John" AND quarter = "Q1"
- • Multi-filter dashboards: Dynamic sums based on multiple dropdowns
SUMIFS Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
1. sum_range (required)
The cells to sum. Must be numbers or blank.
D2:D100 ← Sum values in column DSales[Amount] ← Sum named range or table column2. criteria_range1 (required)
First range to evaluate. Must be same size as sum_range.
A2:A100 ← Check column ARegion[Name] ← Table column reference3. criteria1 (required)
Condition for criteria_range1.
"Apple" ← Exact match">100" ← Number comparison">"&E2 ← Dynamic cell reference4. Additional criteria pairs (optional)
Add more range/criteria pairs. Up to 127 pairs allowed.
criteria_range2, criteria2 ← Second conditioncriteria_range3, criteria3 ← Third condition⚠️ Important Rules
- • Range sizes must match: All criteria ranges must have same dimensions as sum_range
- • AND logic only: All criteria must be true. For OR, use multiple SUMIFS formulas
- • Wildcard support: Use * and ? in text criteria: "*apple*", "test?"
- • Operators in quotes: ">50", "<100", "<>0"
- • Case-insensitive: Text matching ignores case by default
Comparison Operators
=Equal>Greater than<Less than
>=Greater/equal<=Less/equal<>Not equal
*Any characters?One character~Escape wildcard
12+ SUMIFS Examples
1. Two Criteria (Text + Number)
=SUMIFS(D2:D100, A2:A100, "Apple", C2:C100, ">100")
Sum column D where A = "Apple" AND C > 100
2. Three Criteria
=SUMIFS(E2:E100, A2:A100, "North", B2:B100, "Electronics", D2:D100, ">=50")
Sum E where Region="North" AND Category="Electronics" AND Quantity>=50
3. Date Range
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2025,1,1),
A2:A100, "<="&DATE(2025,12,31))Sum C for dates in 2025 (between Jan 1 and Dec 31)
4. Dynamic Cell References
=SUMIFS(D2:D100, A2:A100, F2, B2:B100, G2)
Sum D where A matches F2 AND B matches G2 (criteria from cells)
Wildcard Examples
5. Contains Text
=SUMIFS(C2:C100, A2:A100, "*apple*", B2:B100, ">0")
Sum where A contains "apple" AND B > 0
6. Starts With
=SUMIFS(D2:D100, A2:A100, "North*", C2:C100, "Active")
Sum where A starts with "North" AND C = "Active"
Advanced Examples
7. Between Values
=SUMIFS(D2:D100, C2:C100, ">=50", C2:C100, "<=100")
Sum D where C is between 50 and 100 (same range twice)
8. Not Equal Multiple
=SUMIFS(D2:D100, A2:A100, "<>Pending", B2:B100, "<>Cancelled")
Sum where A ≠ "Pending" AND B ≠ "Cancelled"
9. Current Month
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),
A2:A100, "<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))Sum current month's values dynamically
10. Blank Cells
=SUMIFS(C2:C100, A2:A100, "Completed", B2:B100, "")
Sum where A = "Completed" AND B is blank
OR Logic (Multiple SUMIFS)
11. Sum Multiple Categories
=SUMIFS(C2:C100,A2:A100,"Red")+SUMIFS(C2:C100,A2:A100,"Blue")
Sum where A = "Red" OR A = "Blue"
12. Complex OR + AND
=SUMIFS(D:D,A:A,"North",C:C,">100")+ SUMIFS(D:D,A:A,"South",C:C,">100")
Sum where (Region=North OR South) AND Value>100
🎯 Pro Tips
- • Performance: Use specific ranges (A2:A100) instead of entire columns (A:A)
- • OR logic: Add multiple SUMIFS: =SUMIFS(...)+SUMIFS(...)
- • Dynamic dates: Use TODAY(), EOMONTH(), DATE() for flexible criteria
- • Table references: Use structured references: =SUMIFS(Sales[Amount], Sales[Region], "North")
- • Debugging: Test each criteria pair separately to isolate issues
Frequently Asked Questions
Why is my SUMIFS returning 0?
Common causes: 1) Criteria ranges different size than sum_range, 2) Extra spaces in data (use TRIM), 3) Numbers stored as text, 4) Criteria syntax wrong (missing quotes or &), 5) No values match ALL criteria (remember it's AND logic).
Can I use SUMIFS with OR logic?
No, SUMIFS uses AND logic only. For OR, add multiple SUMIFS: =SUMIFS(C:C,A:A,"Red")+SUMIFS(C:C,A:A,"Blue") sums where A is Red OR Blue. For complex OR/AND combinations, use SUMPRODUCT.
What's the maximum number of criteria in SUMIFS?
Excel allows up to 127 criteria range/criteria pairs in SUMIFS. That's up to 127 conditions using AND logic. In practice, 5-10 criteria is typical. More than that, consider restructuring your data or using pivot tables.
Can SUMIFS sum across multiple sheets?
Not directly. SUMIFS works on one sheet. For multiple sheets, use: =SUMIFS(Sheet1!C:C,Sheet1!A:A,"X")+SUMIFS(Sheet2!C:C,Sheet2!A:A,"X"). Or use 3D reference with SUM and IF array formula.
How do I sum between two dates with SUMIFS?
Use the same date column twice: =SUMIFS(C:C, A:A, ">="&DATE(2025,1,1), A:A, "<="&DATE(2025,12,31)). First criteria: start date, second criteria: end date. Works with any date range.
SUMIFS vs SUMPRODUCT - which is better?
Use SUMIFS when possible - it's faster and simpler. Use SUMPRODUCT for: 1) OR logic combinations, 2) Complex conditions, 3) Array operations. Example: SUMIFS for (A=X AND B>Y), SUMPRODUCT for (A=X OR B>Y).