sum_range - Required. The range of cells to sum (e.g., D:D for all amounts in column D).
criteria_range1 - Required. First range to evaluate against criteria1 (e.g., B:B for region column).
criteria1 - Required. Condition for criteria_range1 (e.g., "East", ">100", "*Pro*").
criteria_range2, criteria2 - Optional. Optional. Additional range/criteria pairs. Up to 127 criteria pairs allowed.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales |
| 2 | Laptop | East | Q1 | 25000 |
| 3 | Mouse | West | Q1 | 5000 |
| 4 | Laptop | East | Q2 | 28000 |
| 5 | Laptop | East | Q1 | 22000 |
| 7 | East Q1 Sales: | =SUMIFS(D:D, B:B, "East", C:C, "Q1") 47000 |
Sum column D where B="East" AND C="Q1"
Returns total where both conditions are true
Sum where B≥100 AND C<1000
Filters numeric ranges with AND logic
Sum where A contains "Pro" AND D="Active"
Partial text matching with multiple criteria
SUMIF tests one criterion, SUMIFS tests many
Use SUMIFS when you need 2+ filters
Analyze sales performance by combining geographic and time filters. This multi-dimensional analysis pattern is impossible with SUMIF alone - it enables cross-tabulation that business analysts, sales managers, and finance teams depend on for quarterly reviews, regional performance tracking, and strategic planning. This example demonstrates how to filter data across two dimensions simultaneously, essential for sales dashboards, revenue reports, territory analysis, and any business scenario requiring segmented aggregation across multiple categorical variables. Real-world applications include tracking product performance by market segment, comparing year-over-year growth by sales territory, monitoring KPIs across departments and time periods, and creating pivot-style summary reports without pivot tables. The ability to combine categorical filters (region, product line, customer tier) with temporal filters (quarter, month, fiscal period) makes this approach indispensable for management reporting, executive dashboards, and data-driven decision making across organizations.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Region | Quarter | Amount |
| 2 | ORD-001 | East | Q1 | $25,000 |
| 3 | ORD-002 | West | Q1 | $32,000 |
| 4 | ORD-003 | East | Q2 | $28,000 |
| 5 | ORD-004 | East | Q1 | $18,000 |
| 6 | ORD-005 | West | Q2 | $41,000 |
| 7 | East Q1 Total: | =SUMIFS(D:D, B:B, "East", C:C, "Q1") $43,000 |
Track departmental expenses within specific date ranges using comparison operators. Date-based filtering is essential for monthly budget reports, fiscal period analysis, and rolling forecasts. Finance managers and budget analysts use this pattern to calculate period-specific costs, compare actual vs budget across departments, and identify spending trends. This technique combines categorical filters (department) with temporal filters (date ranges) for comprehensive financial visibility and control. Common use cases include monitoring marketing spend by campaign type and month, tracking operational expenses by cost center and fiscal quarter, analyzing payroll costs by department and pay period, calculating project expenses by phase and timeline milestone. The dual-range capability (using the same date column twice with >= and <= operators) makes it perfect for any scenario requiring "between dates" logic combined with categorical segmentation - a pattern used daily in corporate finance, budget variance analysis, forecasting models, and compliance reporting.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Department | Category | Amount |
| 2 | 2025-01-05 | Marketing | Advertising | $5,000 |
| 3 | 2025-01-10 | Marketing | Software | $1,200 |
| 4 | 2025-01-15 | Sales | Travel | $3,500 |
| 5 | 2025-01-20 | Marketing | Advertising | $4,200 |
| 6 | Marketing Ad Spend Jan: | =SUMIFS(D:D, B:B, "Marketing", C:C, "Advertising", A:A, ">=2025-01-01", A:A, "<=2025-01-31") $9,200 |
❌ The Problem:
✅ Solution:
=SUMIFS(D:D, B:B, "East", C:C, "Q1")Correct syntax requires sum_range first, then criteria_range/criteria pairs. Each criteria must immediately follow its range. This is opposite of SUMIF where criteria comes before range - a common source of confusion when transitioning between the two functions.
❌ The Problem:
✅ Solution:
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Q1")All ranges must be exactly the same size - same number of rows and columns. It checks each row position across all ranges simultaneously. Use consistent range references like D2:D100, B2:B100, C2:C100, or use entire columns (D:D, B:B, C:C) which automatically match.
❌ The Problem:
✅ Solution:
=SUMIFS(D:D, B:B, "East", C:C, "Q1") + SUMIFS(D:D, B:B, "West", C:C, "Q1")This formula uses AND logic exclusively - all conditions must be met. For OR logic (East OR West), add multiple formulas together. Alternatively, use SUMPRODUCT for more complex logic: =SUMPRODUCT((B:B="East")+(B:B="West"))*(C:C="Q1")*(D:D)). See <Link href="/formulas/sumproduct">SUMPRODUCT</Link> for advanced OR/AND combinations.
Master these SUMIFS variants:
From basics to advanced - AI generates perfect formulas instantly.