Recommended Free Tools
SUMIF adds numbers only when a related cell meets one condition. That makes it useful for personal-finance sheets such as category budgets, transaction logs, property commissions, and date-based income reports.
Its basic syntax is:
=SUMIF(range, criteria, [sum_range])range is the cells Excel checks, criteria is the condition, and sum_range is the cells to add. The final argument is optional. If you leave it out, Excel adds matching cells from range itself.
How to enter a SUMIF formula
- Select the cell where you want the result.
- Type
=SUMIF(in the cell or Formula Bar. - Enter the range, condition, and optional sum range, separated by commas in US-English Excel.
- Type
)and press Enter.
Excel supports SUMIF in Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including supported Mac editions. You can also open the function wizard through Formulas > Insert Function, or press Shift+F3.
For a transaction sheet, imagine that column A contains a category, column B contains the transaction date, and column C contains the amount.
| Row | Category | Date | Amount |
|---|---|---|---|
| 2 | Groceries | 1/4/2026 | 85 |
| 3 | Utilities | 1/7/2026 | 120 |
| 4 | Groceries | 1/12/2026 | 42 |
| 5 | Transport | 1/15/2026 | 60 |
Example 1: Add values greater than a number
Suppose B2:B25 contains expense amounts and you want to add only amounts greater than $5:
=SUMIF(B2:B25,">5")Because sum_range is omitted, Excel checks and sums the same cells. The comparison operator must be inside quotation marks. The same rule applies to <, >=, <=, <>, and =.
This can be useful when you want to exclude small incidental purchases from a quick spending analysis.
Example 2: Sum amounts for a matching category
To total sales or expenses in column C where the category in column A is Fruits, use:
=SUMIF(A2:A7,"Fruits",C2:C7)Excel examines A2:A7. Each time it finds Fruits, it adds the value from the corresponding row in C2:C7.
For a personal budget, the equivalent formula might be:
=SUMIF(A2:A100,"Groceries",C2:C100)This gives the total grocery spending without manually filtering the transaction list.
Example 3: Sum values equal to a number
Assume column A contains property values and column B contains commissions. To add commissions for properties valued at exactly 300,000:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →=SUMIF(A2:A5,300000,B2:B5)Numeric criteria do not need quotation marks. This text-form version also works:
=SUMIF(A2:A5,"=300000",B2:B5)Use the numeric version when the source values are genuine numbers. If values that look numeric were imported as text, the formula may not match them as expected; convert the source column to numbers before troubleshooting the formula.
Example 4: Use a cell reference as the criterion
Suppose C2 contains a spending threshold, such as 100. To add amounts in B2:B5 that are greater than that threshold, while checking the corresponding values in A2:A5, use:
=SUMIF(A2:A5,">"&C2,B2:B5)The operator is quoted and joined to the cell reference with &. Do not write:
=SUMIF(A2:A5,">C2",B2:B5)">C2" treats C2 as literal text. It does not use the value stored in that cell.
This pattern is useful for a dashboard where you change a threshold in one input cell rather than editing several formulas.
Example 5: Match text with wildcards
SUMIF supports wildcards in text criteria. To add values in C2:C7 when the corresponding text in B2:B7 ends with es:
=SUMIF(B2:B7,"*es",C2:C7)The wildcard symbols work as follows:
| Pattern | Meaning | Example |
|---|---|---|
* |
Any sequence of characters, including no characters | "A*" matches text beginning with A |
? |
Exactly one character | "To?" matches three-character text beginning with To |
~* |
A literal asterisk | Matches text ending with * |
~? |
A literal question mark | Matches text ending with ? |
Examples:
=SUMIF(B2:B10,"A*",C2:C10)
=SUMIF(B2:B10,"To?",C2:C10)
=SUMIF(B2:B10,"*~?",C2:C10)Wildcards can help when merchant descriptions are inconsistent, such as transaction labels that begin with the same store name but contain different location codes.
Free tools Windows power users keep installed
One-click scans. No signup required.
Example 6: Add rows where the criterion cell is blank
To total sales in column C where the category in column A is blank, use:
=SUMIF(A2:A7,"",C2:C7)The empty-string criterion matches cells with no value. In a budget, this can identify spending that has not yet been assigned to a category.
Be careful with “blank-looking” cells. A cell containing a formula that returns "" is not always treated identically to a genuinely empty cell in broader formula logic. If the result seems wrong, inspect the source cells and test a known blank row.
Example 7: Sum amounts on or after a date
If dates are in A2:A100 and amounts are in C2:C100, this formula adds amounts dated January 1, 2026 or later:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →=SUMIF(A2:A100,">="&DATE(2026,1,1),C2:C100)DATE(2026,1,1) creates a real Excel date serial number. Combining it with the operator using & avoids ambiguity caused by typed date text and regional date formats.
For a reusable report, put the start date in E2 and use:
=SUMIF(A2:A100,">="&E2,C2:C100)Make sure the dates in column A are real Excel dates, not text that merely looks like a date. You can test a date with =ISNUMBER(A2); a genuine Excel date normally returns TRUE.
SUMIF versus SUMIFS
Use SUMIF for one condition. Use SUMIFS when the total must satisfy two or more conditions—for example, grocery spending in January, or income from one client after a particular date.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)The argument order is different:
=SUMIF(criteria_range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range1, criteria1, ...)For example, a two-condition total might be:
=SUMIFS(C2:C100,A2:A100,"Groceries",B2:B100,">="&DATE(2026,1,1))Do not copy the SUMIFS argument order into a SUMIF formula. SUMIFS supports up to 127 range-and-criteria pairs, while SUMIF is intended for a single condition.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Common SUMIF errors and silent problems
| Problem | What to check |
|---|---|
| Missing quotation marks | Use ">5", not >5. |
| Reference treated as text | Use ">"&C2, not ">C2". |
| Wrong argument order | SUMIF puts the criteria range first and sum range third; SUMIFS puts sum range first. |
| Mismatched ranges | Keep range and sum_range the same size and aligned to the same rows. |
| Numbers or dates stored as text | Convert imported text to real numbers or Excel dates. |
| Unexpected external-workbook error | SUMIF can return #VALUE! when it refers to calculated cells in a closed external workbook. Open the source workbook or use the documented array-formula workaround. |
| Very long criteria | Matching strings longer than 255 characters, or matching the string #VALUE!, can produce incorrect results. |
Mismatched ranges are especially risky because Excel may not return an error. If the ranges differ in size, Excel can begin at the top-left cell of sum_range and use a range matching the dimensions of range. That can silently add the wrong rows and may reduce performance. Use aligned ranges such as A2:A100 and C2:C100.
FAQ
Can SUMIF use more than one condition?
Not directly. SUMIF handles one condition. Use SUMIFS for two or more conditions, such as category plus date.
Do I need to include sum_range in SUMIF?
No. If you omit it, Excel sums matching cells in the criteria range itself. Include sum_range when one column is checked and another column is added.
Why does my SUMIF formula show a syntax error?
Check that comparison criteria are quoted, such as ">5", and that cell-reference criteria use concatenation, such as ">"&C2. Also check your regional list separator; some Excel installations use semicolons instead of commas.
Why is SUMIF returning the wrong total?
Check for mismatched ranges, numbers or dates stored as text, hidden spaces in text labels, and blank-looking formula results. Also verify that you used SUMIF’s argument order rather than SUMIFS’s order.
The Bottom Line
Use SUMIF when one condition determines which values to add: a category, threshold, text pattern, blank cell, or date boundary. The reliable pattern is =SUMIF(criteria_range, criteria, sum_range). Quote comparison operators, join dynamic criteria with &, keep the ranges aligned, and switch to SUMIFS when the calculation needs multiple conditions.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
