Quick Navigation
SUMIF Function
Summary
The SUMIF function in Excel sums values in a specified range that meet single criteria you define. It's perfect for conditional totaling like summing only sales above a target or commissions for specific regions, making data analysis quick and targeted.
Syntax
SUMIF(range, criteria, [sum_range])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| range | Range |
Yes | The range evaluated by criteria. Must contain numbers, names, arrays, or date references. |
| criteria | Any |
Yes | Defines which cells to sum using numbers, expressions, text, cell refs, or wildcards. |
| sum_range | Range |
No | Optional range to sum. If omitted, uses the range argument. |
Using the SUMIF Function
SUMIF excels at single-condition aggregation across datasets. Use it to total sales by category, filter high-value transactions, or compute region-specific metrics without manual filtering.
Common SUMIF Examples
Sum commissions for high-value properties
=SUMIF(A2:A5,">160000",B2:B5)Totals commissions ($63,000) where property values exceed $160,000.
Category-based sales total
=SUMIF(A2:A7,"Fruits",C2:C7)Sums sales ($2,000) for all fruits category items.
Wildcard pattern matching
=SUMIF(B2:B7,"*es",C2:C7)Sums sales ($4,300) for foods ending in 'es' (Tomatoes, Oranges, Apples).
Dynamic criteria from cell
=SUMIF(A2:A5,">"&D1,B2:B5)Sums commissions where property values exceed cell D1.
Frequently Asked Questions
Common Errors and Solutions
Incorrect results with long strings
Cause: Matching strings >255 characters or #VALUE!
Solution: Use SUMIFS or alternative methods for long text matching
Unexpected sums
Cause: sum_range size/shape mismatch with range
Solution: Ensure both ranges have identical dimensions
#VALUE! error
Cause: criteria references invalid ranges or wrong data types
Solution: Verify criteria syntax and cell references
Notes
- Wildcards: ? (single char), * (sequence), ~? or ~* (literal ? or *)
- Blank criteria "" matches empty cells
- Omit sum_range to sum the criteria range itself
- Available since Excel 2007+
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+