range - Required. The range to evaluate with criteria (e.g., A1:A10 for category column).
criteria - Required. The condition that determines which cells to sum (e.g., "East", ">100", or cell reference like E2).
sum_range - Optional. Optional. The actual cells to sum. If omitted, Excel sums the cells in range that meet the criteria.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Person | Sales |
| 2 | East | Alice | 45000 |
| 3 | West | Bob | 38000 |
| 4 | East | Carol | 52000 |
| 5 | East | Dave | 38000 |
| 6 | East Total: | =SUMIF(A:A, "East", C:C) 135000 |
Sum column B where column A equals "East"
Adds all sales for East region
Sum column B where column A is greater than 100
Total of values where quantity exceeds 100
Sum B where A contains "Pro" anywhere in text
Totals for all products with "Pro" in name
Sum B where A equals the value in cell E2
Dynamic criteria from another cell
Calculate total sales for a specific region using conditional summation. This formula automatically finds and sums all matching entries, eliminating manual filtering and calculation errors. This pattern is essential for sales managers tracking regional performance, finance teams analyzing geographic revenue distribution, and business analysts building territory reports. It handles dynamic data ranges, making it perfect for ongoing sales tracking where new transactions are added daily. Use this conditional sum technique for any categorical aggregation: sum by salesperson, product line, customer segment, or any classification dimension in your business data.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Person | Sales |
| 2 | East | Alice | $45,000 |
| 3 | West | Bob | $38,000 |
| 4 | East | Carol | $52,000 |
| 5 | East | Dave | $38,000 |
| 6 | East Total: | =SUMIF(A:A, "East", C:C) $135,000 |
Sum expenses that exceed a specific amount with comparison operators. Using SUMIF with operators (>, <, >=, <=) enables threshold-based analysis critical for budget management and financial control. Finance departments use this pattern to identify high-value transactions requiring special approval, audit teams flag expenses exceeding policy limits, and procurement managers track large purchases for vendor negotiations. This function with numeric criteria supports all standard comparisons, making it versatile for variance analysis, outlier detection, and compliance monitoring across financial workflows.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | |
| 2 | Travel | $450 | |
| 3 | Supplies | $75 | |
| 4 | Software | $1,200 | |
| 5 | Marketing | $850 | |
| 6 | Over $500: | $2,050 | =SUMIF(B:B, ">500") |
❌ The Problem:
✅ Solution:
=SUMIF(A:A, "East", B:B)Always wrap text criteria in double quotes. This formula requires quotes for literal text values. Only omit quotes when using cell references (=SUMIF(A:A, E2, B:B)) or numeric comparisons. This is a fundamental syntax rule that prevents #NAME? errors and ensures your conditional sum formulas work reliably across all scenarios.
❌ The Problem:
✅ Solution:
=SUMIF(B:B, ">100")The first argument (range) should be the column you're testing with criteria. When summing values greater than 100, put the numeric column in range parameter. This formula uses range for criteria evaluation, and sum_range for actual summing. If you omit sum_range, it sums the cells in range that meet your condition - perfect for this scenario.
❌ The Problem:
✅ Solution:
=SUMIF(A1:A10, "East", C1:C10)Always use matching sizes for range and sum_range. This formula aligns ranges by relative position - if range starts at A1, sum_range should start at the corresponding row in column C. When ranges are different sizes, only the overlapping portion is used, causing missing data in your conditional sums. Best practice: use whole column references (A:A, C:C) to avoid size mismatches entirely.
Master these SUMIF variants:
From basics to advanced - AI generates perfect formulas instantly.