SUMIF Function
SUMIF adds values that match a condition. Give it a range to check, a condition to test, and a range of numbers to sum — it only adds the ones that pass the test.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| range | The range of cells to evaluate with the criteria. Each cell is tested against the condition. | Required |
| criteria | The condition that determines which cells to sum. Can be a number, text, comparison (>100, "<>0"), or wildcard (*apples*). | Required |
| sum_range | The actual cells to sum. If omitted, the 'range' itself is summed (must contain numbers). | Optional |
Basic Example
Sum all sales from the 'East' region
=SUMIF(A2:A50, "East", B2:B50)Column A contains region names. The formula checks each cell for 'East' and sums the corresponding sales values from column B.
Advanced Examples
Example 1: Sum values greater than a threshold
BudgetingSum all expenses that exceed $500
=SUMIF(B2:B100, ">500")Example 2: SUMIF with wildcard for partial match
Sales analysisSum sales for all products containing 'phone' in the name
=SUMIF(A2:A100, "*phone*", B2:B100)Example 3: SUMIF with date criteria
ReportingSum transactions that occurred after a specific date
=SUMIF(A2:A200, ">"&DATE(2024,1,1), B2:B200)How SUMIF Works
SUMIF works in two phases. First, it scans the 'range' cell by cell, checking each against your criteria. For text criteria, it does an exact match (case-insensitive) unless you use wildcards. For number criteria with operators (>100, <=50), it evaluates the comparison. Second, for cells that pass the test, it takes the corresponding cell from 'sum_range' (or from 'range' itself if sum_range is omitted) and adds them all together.
Important Notes & Limitations
SUMIF handles only one condition. For multiple conditions (e.g., region='East' AND year=2024), use SUMIFS.
Text criteria are case-insensitive — 'east' and 'East' match the same cells.
SUMIF doesn't work well with 2D ranges — both range and sum_range should be 1D (single row or column).
Criteria must be a string, number, or cell reference — you can't pass a complex logical expression directly.
Common Errors & Fixes
0 result (wrong sum_range size)sum_range is a different size than range — mismatched dimensionsFix: Make sure sum_range has the same number of rows/columns as range. If range is A2:A50, sum_range should be B2:B50 (not B1:B49).
0 result (numbers stored as text)The sum_range contains numbers formatted as text, which SUMIF ignoresFix: Convert text numbers to actual numbers using VALUE() or reformatting the cells as Number.
Wrong result (criteria text mismatch)Trailing spaces or different formatting in the criteria rangeFix: Use TRIM() to clean data, or use wildcards like "*East*" to handle minor differences.
Download Practice File
SUMIF
sumifPractice SUMIF with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the SUMIF function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps
Related Tutorials
AVERAGEIF with Criteria
Learn how to use AVERAGEIF with criteria in Excel and Google Sheets: syntax, text, number and date conditions, wildcards, and when to switch to AVERAGEIFS.
Excel Budget Template
Build an Excel budget template from scratch: structure your sheets, total spending by category with SUMIF, flag overspending with IF, and track averages.
Excel Expense Tracker
Build an Excel expense tracker from scratch: log spending on one sheet, then use SUMIF, SUMIFS, SUM and TODAY for category totals and month-to-date spend.
Excel Invoice Template
Build a working Excel invoice template with auto-calculating totals, VLOOKUP price lookups, and TEXT-formatted dates - a practical guide for small businesses.
Excel Sales Dashboard: Build One That Updates Itself
Build an Excel sales dashboard that keeps itself current: one flat sales table, SUMIFS KPIs, XLOOKUP targets and FILTER drill-downs that grow with the data.
How to Use Excel Formulas: From the First Equals Sign to a Real Report
Learn how to use Excel formulas step by step: the equals sign, cell references, SUM, IF and VLOOKUP, plus the errors beginners hit and how to fix them.
SUMIF with a Date Range
SUMIF only takes one condition, so summing between two dates needs a trick. Learn three clean ways to SUMIF (or SUMIFS) over a date range in Excel and Google Sheets.
SUMIFS Multiple Criteria
Build SUMIFS multiple criteria formulas that total values matching two or more conditions in Excel and Google Sheets, including dates, wildcards and OR logic.
SUMPRODUCT with Multiple Conditions
Need more conditions than SUMIFS handles? Learn how SUMPRODUCT with multiple conditions solves AND, OR, case-sensitive and partial-text sums in Excel.