Free Excel workbook
Get the SUMIF Practice Workbook
Download the ready-to-use workbook and practice the exact formulas from the tutorial.
- Practice SUMIF with text, greater-than rules, and date conditions
- See wildcard and logical operator examples already set up
- Follow the tutorial without building the examples from scratch
Enter your email below for instant access.
Free. Instant access.
Imagine you have a list of weekly grocery spends. You only want to see how much you spent on fruits. SUMIF can help you add up just those numbers.
SUMIF Syntax and Arguments Explained
Here’s how you write a SUMIF formula:
=SUMIF(range, criteria, [sum_range])- “range“: This is the place to look: Where is your data? For example, in which column are your items listed?.
- “criteria“: What to look for: What item or number are you interested in? This could be “Fruits” or any amount like numbers over 50.
- [sum_range]: Where to add (optional): Where are the numbers you want to add up? If you leave this out, Excel uses the first range you gave it.
How to Sum by Text Criteria in SUMIF
Suppose you have a list where column B lists items you’ve purchased, and column C lists their costs. Here’s a sample data set:
You want to find out how much you spent specifically on fruits. This is the SUMIF formula you’d write:
=SUMIF(B4:B9, "Fruits", C4:C9)- B4:B9: this range contains the items purchased.
- “Fruits”: Excel looks for cells that contain exactly the word “Fruits”. The quotation marks around Fruits signify that it is text.
- C4:C9: Excel sums the values in column B that correspond to rows where column A has “Fruits”.
How to Use SUMIF with Greater Than, Less Than, and Not Equal To
Logical operators are powerful tools in Excel that allow you to perform calculations based on specific conditions. When combined with SUMIF, logical operators let you sum values meeting certain criteria. For example, amounts greater than or less than a specified number.
Let’s assume you have the same spreadsheet where column B lists items, and column C lists their associated costs. You want to find out the total cost of items where each individual cost is greater than $10.
Here is the SUMIF formula you can use:
=SUMIF(C4:C9, ">10", C4:C9)- C4:C9 is the range where Excel checks the condition.
- “>10” is the condition that selects cells with values greater than 10.
- C4:C9 is also the range to sum (the same as the condition range, meaning it sums the values that meet the condition).
❗ Ensure that the logical operator and the number are enclosed in quotation marks (e.g., “>10”). If you separate them or leave them out of quotes, Excel will not recognize the condition.
- SUMIF examines each cell in C4 through C9. Then it adds up the cells that meet the condition of being greater than 10: $15 + $20 = $35
More Examples with Logical Operators
The table below outlines simple syntax examples. You can use them for common tasks.
| Target | Logical Operator |
|---|---|
| Greater than 10 | “>10” |
| Equal to 20 | “=20” |
| Less than 10 | “<10” |
| Greater or equal to 15 | “>=15” |
| Not equal to 10 | “<>10” |
| Cells that are blank | “” |
| Cells that are not blank | “<>” |
This way you can use SUMIF with logical operators like ‘greater than’, ‘less than’, or ‘equal to’. You can also sum cells that are ‘blank’ or ‘not blank’.
How to Use SUMIF with Wildcards for Partial Matches
Wildcards are special characters. They help you make Excel formulas like SUMIF more flexible. They’re particularly useful when you need to match patterns rather than exact text. There are three main wildcards used in Excel:
- ‘*‘ (asterisk): This symbol can stand for any group of letters. For example, “Ap*” would match “Apple”, “Application”, “April”, etc.
- ‘?‘ (question mark): Represents any single letter. For example, “Ap?le” would match “Apple” but not “Aple” or “ApplePie”.
Suppose you have a list where column A lists product names, and column B lists sales figures. You want to sum the sales figures for products that start with “Ap“.
Here is the SUMIF Formula Using Wildcards:
=SUMIF(B4:B8, "Ap*", C4:C8)- It looks at your list from B4 to B8.
- It finds any product name that starts with “Ap”.
- Excel SUMIF adds up the sales amounts from C4 to C8 for these products.
- Total Sum: Excel SUMIF adds up $15 + $20 + $30 + $25 = $90.
How to Use SUMIF with Dates (Before, After, Between)
Using SUMIF with dates in Excel allows you to sum values based on specific time periods. This can be very useful for tracking expenses, sales, or other metrics over a set timeframe.
Imagine you have a list of sales dates and corresponding sales amounts. You want to sum all sales that occurred before April 2024.
Here is the SUMIF Formula Using Dates:
=SUMIF(B4:B8, "<2024-04-01", C4:C8)
💡 If the SUMIF formula does not get you the correct results, it might be that Excel does not recognize the dates.
Excel sees dates as numbers. In such cases, you can combine the Excel SUMIF formula with the Excel DATE function. The DATE function ensures Excel gets the date right, avoiding format confusion.
Here is the updated SUMIF formula:
=SUMIF(B4:B8, "<"&DATE(2024,4,1), C4:C8)
Common SUMIF Errors and How to Fix Them
- Exact Matches: If you type “Food” in your SUMIF function, Excel will look for cells that say exactly “Food.” It won’t count cells that say “Food Expenses.” Use Wildcards when you want to look for partial matches.
- Check Your Criteria: Make sure you put quotes around words (“Food”) and include symbols directly with numbers (“>10”).
- Correct Your Sum Range: If the numbers don’t add up right, double-check that your sum range matches the range you’re examining.
How to Use a Cell Reference as SUMIF Criteria
Instead of hardcoding criteria like “Fruits” directly in the formula, reference a cell. This makes the formula dynamic and reusable.
=SUMIF(B4:B9, E1, C4:C9)Where E1 contains the word “Fruits”. Change E1 to “Vegetables” and the sum updates automatically. No formula edits needed.
Cell reference with a logical operator: When combining a cell reference with an operator like > or <, you must concatenate them with &. The operator goes in quotes, the cell reference does not:
=SUMIF(C4:C9, ">"&E1, C4:C9)If E1 contains 10, Excel reads this as “>10” and sums all values greater than 10.
Common mistake: Writing =SUMIF(C4:C9, “>E1”, C4:C9) does not work. Excel treats “>E1” as literal text, not a reference. Always use the & operator to join the comparison symbol with the cell.
How to Sum Blank or Non-Blank Cells with SUMIF
Sum values where another column is blank:
=SUMIF(B4:B9, "", C4:C9)The empty string “” matches cells that are truly empty (no content, no formula returning blank). Excel sums the corresponding values from C4:C9.
Sum values where another column is not blank:
=SUMIF(B4:B9, "<>", C4:C9)The “<>” operator means “not equal to nothing,” which matches any cell that contains a value, text, or formula result.
Important: If a cell appears blank but contains a formula that returns an empty string (like =IF(A1=1,””,””)), SUMIF with “” will not match it.
That cell is not truly empty. To handle this, use:
=SUMIF(B4:B9, "", C4:C9) + SUMPRODUCT((LEN(B4:B9)=0)(B4:B9<>"")(C4:C9)) Or clean your data first.
SUMIF vs SUMIFS: What Is the Difference?
SUMIF handles one condition. SUMIFS handles multiple conditions using AND logic (all conditions must be true for a row to be included).
| Feature | SUMIF | SUMIFS |
|---|---|---|
| Number of conditions | 1 | 1 or more (up to 127) |
| Argument order | range, criteria, [sum_range] | sum_range, criteria_range1, criteria1, … |
| Sum range position | Third argument (optional) | First argument (required) |
| Wildcard support | Yes | Yes |
| Date criteria | Yes | Yes |
| OR logic | Not built-in (use helper formulas) | Not built-in (use SUMPRODUCT) |
Key syntax difference: In SUMIF, the sum_range comes last and is optional. In SUMIFS, the sum_range comes first and is required. This catches many people when switching between the two.
If you need to sum with two or more conditions (e.g., “Fruits” AND “January”), use SUMIFS. See our full guide on SUMIFS, COUNTIFS, and AVERAGEIFS.
Frequently Asked Questions
Is SUMIF case-sensitive?
No. SUMIF treats “Fruits”, “fruits”, and “FRUITS” as the same value. If you need a case-sensitive conditional sum, use SUMPRODUCT with the EXACT function instead: =SUMPRODUCT((EXACT(B4:B9, “Fruits”))*(C4:C9))
Can SUMIF handle multiple criteria?
No. SUMIF supports only one condition. For multiple conditions, use SUMIFS. For OR logic (e.g., sum “Fruits” or “Dairy”), add separate SUMIF formulas: =SUMIF(B4:B9, “Fruits”, C4:C9) + SUMIF(B4:B9, “Dairy”, C4:C9)
Why does SUMIF return 0 when matches exist?
The most common cause is a data type mismatch. If your criteria is a number but the range contains numbers stored as text (or vice versa), SUMIF will not match. Check for leading spaces, trailing spaces, or apostrophes before numbers. Use TRIM and CLEAN to standardize the data.
Does SUMIF work with dates?
Yes. Use the DATE function to avoid regional format issues: =SUMIF(B4:B8, “<“&DATE(2024,4,1), C4:C8). This sums all values where the date in column B is before April 1, 2024.
What is the 255-character limit in SUMIF?
SUMIF returns incorrect results when the criteria string is longer than 255 characters. This is a documented limitation. If you need to match text longer than 255 characters, use SUMPRODUCT with an EXACT or FIND formula instead.
Download the workbook
Download the SUMIF Practice Workbook
Download Free WorkbookWant to build confidence with all essential Excel formulas? Check out the Excel Essentials for the Real World course for hands-on, real-world training.
Featured Course
Excel Essentials for the Real World
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
