Excel SUMIF: Syntax and Examples
SUMIF is Excel's workhorse function for conditional summation. Instead of manually filtering data and adding up values, SUMIF evaluates a range of cells against a condition and sums corresponding...
Key Insights
- SUMIF adds values that meet a single condition, with syntax
=SUMIF(range, criteria, [sum_range])where the sum_range is optional when evaluating and summing the same cells - Criteria can include comparison operators (>, <, =), wildcards (* and ?), or cell references concatenated with ampersands for dynamic formulas
- Common errors stem from mismatched range sizes, text-formatted numbers, and forgetting to concatenate operators with cell references using the & symbol
Introduction to SUMIF
SUMIF is Excel’s workhorse function for conditional summation. Instead of manually filtering data and adding up values, SUMIF evaluates a range of cells against a condition and sums corresponding values in one formula. This makes it indispensable for financial reports, sales analysis, inventory management, and any scenario where you need totals based on specific criteria.
The function shines in situations like calculating total sales for a specific region, summing expenses above a threshold, or aggregating revenue for particular product categories. Unlike basic SUM, which adds everything indiscriminately, SUMIF gives you precision control over what gets included in your calculation.
SUMIF Syntax Breakdown
The SUMIF function accepts three parameters, two required and one optional:
=SUMIF(range, criteria, [sum_range])
range: The cells you want to evaluate against your criteria. This is where Excel looks to determine which rows meet your condition. Could be product names, dates, regions, or any data type.
criteria: The condition that determines which cells to include. This can be a number, text, expression, cell reference, or comparison operator. Examples include “>100”, “North”, or a reference like A1.
sum_range: Optional. The actual cells to sum if different from the range parameter. When omitted, Excel sums the cells specified in the range parameter. Use this when your criteria column differs from your values column—like evaluating product names but summing prices.
The key concept: range and criteria work together to identify which rows qualify, then sum_range determines what values to add from those rows.
Basic SUMIF Examples
Let’s work through fundamental examples that cover the most common use cases.
Sum values greater than a threshold:
=SUMIF(A2:A10, ">100", B2:B10)
This evaluates cells A2 through A10, finds those greater than 100, and sums the corresponding values in B2:B10. If A5 contains 150, Excel includes B5 in the sum.
Sum values matching specific text:
=SUMIF(C2:C10, "Apples", D2:D10)
Looks through C2:C10 for cells containing exactly “Apples” and sums the matching values from D2:D10. Text criteria are case-insensitive, so “apples” and “APPLES” both match.
Sum based on date criteria:
=SUMIF(E2:E10, ">1/1/2024", F2:F10)
Finds dates in E2:E10 after January 1, 2024, and sums corresponding values in F2:F10. Excel handles date comparisons naturally since dates are stored as numbers internally.
When range and sum_range are identical:
=SUMIF(A2:A10, ">500")
If you’re evaluating and summing the same cells, omit the third parameter. This formula sums all values in A2:A10 that exceed 500.
Advanced SUMIF Techniques
Once you master the basics, these advanced techniques unlock SUMIF’s full potential.
Wildcard matching with asterisk (*):
=SUMIF(A2:A10, "North*", B2:B10)
The asterisk matches any sequence of characters. This sums values where column A starts with “North”—matching “North”, “Northern”, “North America”, etc. Perfect for partial text matching.
Wildcard matching with question mark (?):
=SUMIF(A2:A10, "Q? 2024", B2:B10)
The question mark matches exactly one character. This matches “Q1 2024”, “Q2 2024”, “Q3 2024”, and “Q4 2024”, but not “Quarter 2024”.
Cell references as criteria:
=SUMIF(A2:A10, C1, B2:B10)
Instead of hardcoding criteria, reference another cell. When C1 contains “West”, this sums all Western region sales. Change C1 to “East”, and the formula updates automatically—ideal for dynamic dashboards.
Concatenating operators with cell references:
=SUMIF(A2:A10, ">"&D1, B2:B10)
To use comparison operators with cell references, concatenate them using the ampersand. If D1 contains 1000, this becomes “sum values where column A exceeds the value in D1”. Without the ampersand and quotes, Excel throws an error.
Dynamic date criteria with TODAY:
=SUMIF(A2:A10, "<"&TODAY(), B2:B10)
Sums values where the date in column A is before today. This formula updates automatically each day, perfect for calculating overdue amounts or historical totals.
Combining with EOMONTH for month-end calculations:
=SUMIF(A2:A10, "<="&EOMONTH(TODAY(), 0), B2:B10)
Sums values through the end of the current month. EOMONTH returns the last day of the month, enabling sophisticated date-based analysis.
Not equal to criteria:
=SUMIF(A2:A10, "<>Pending", B2:B10)
The “<>” operator means “not equal to”. This sums all values except those where column A contains “Pending”.
Common Pitfalls and Troubleshooting
Mismatched range sizes cause #VALUE! errors:
❌ =SUMIF(A2:A10, ">100", B2:B15)
✅ =SUMIF(A2:A10, ">100", B2:B10)
The range and sum_range must contain the same number of rows. The first formula fails because A2:A10 has 9 rows while B2:B15 has 14. Excel can’t map the ranges correctly.
Text-formatted numbers won’t evaluate numerically:
If column A contains numbers stored as text, =SUMIF(A2:A10, ">100", B2:B10) may return 0 or unexpected results. Text “100” doesn’t evaluate as greater than or less than anything. Fix this by converting text to numbers using VALUE() or by reimporting data correctly.
Forgetting ampersand with operators and cell references:
❌ =SUMIF(A2:A10, ">D1", B2:B10)
✅ =SUMIF(A2:A10, ">"&D1, B2:B10)
The first formula literally looks for cells containing the text “>D1”, not cells greater than D1’s value. Always concatenate operators with cell references.
Case sensitivity doesn’t exist in SUMIF:
SUMIF treats “Apple”, “apple”, and “APPLE” identically. If you need case-sensitive matching, you’ll need an array formula with SUM and EXACT functions instead.
Criteria with leading/trailing spaces:
If your criteria is “North” but cells contain “North " (with a trailing space), SUMIF won’t match. Use TRIM() to clean data or adjust your criteria to account for spaces.
SUMIF vs. SUMIFS
SUMIF handles one condition. When you need multiple criteria, use SUMIFS instead. The syntax differs slightly—SUMIFS puts sum_range first:
Converting SUMIF to SUMIFS:
SUMIF version (single criterion):
=SUMIF(A2:A10, "North", B2:B10)
SUMIFS version (multiple criteria):
=SUMIFS(B2:B10, A2:A10, "North", C2:C10, ">1000")
This SUMIFS formula sums B2:B10 where column A equals “North” AND column C exceeds 1000. Notice the sum_range comes first in SUMIFS, followed by criteria_range/criteria pairs.
Use SUMIF when you have a single condition—it’s simpler and more readable. Switch to SUMIFS when you need multiple conditions evaluated simultaneously. Don’t try chaining multiple SUMIF functions with addition; SUMIFS is cleaner and more efficient.
Practical example comparing both:
Single criterion - use SUMIF:
=SUMIF(Region, "West", Sales)
Multiple criteria - use SUMIFS:
=SUMIFS(Sales, Region, "West", Product, "Laptop", Date, ">="&DATE(2024,1,1))
The SUMIFS example sums sales for Western region laptops sold since January 1, 2024—three conditions that SUMIF cannot handle.
Conclusion
SUMIF transforms raw data into actionable insights with minimal effort. Master the syntax, understand how criteria work, and learn to concatenate operators with cell references. Avoid the common pitfalls of mismatched ranges and text-formatted numbers. When you need multiple conditions, graduate to SUMIFS. With these techniques, you’ll build dynamic, powerful spreadsheets that calculate exactly what you need.